Date Logic

[has75042]

HI All,

My source data has STCLDT (store close date) is a 6 digit numeric filed in YYMMDD format, so if a store closed in December in the year 2000, the format would be 00125. Can't use makedate() function and compare dates. I am using a below logic to change into date format.

So if the store close date number is less than 1300, it defaults to 010101, which is a number than can be converted to a date easily.

if(STCLDT < 1300 and STCLDT <> 0, 010101, STCLDT) as Close_Date,

 Then, the below logic looks to see if there is a close date. if the numeric filed is zero, the store is open      

if(Close_Date = 0, 'N', 'Y') as Str_Closed,

So in source data, if some enters a  store closing date for next month.  I wanted to make above flag it in a way that the flag should still read as open store until 1 week or few days from the actual closing date.

[dplr-rn]

how about
f(Close_Date = 0, 'N',
if(today()<Date#(Close_Date ,'YYMMDD'),'N','Y') ) as Str_Closed
basically if close date is greater than today mark flag as 'N'
hope i understoof correctly

[jduenyas]

"... is a 6 digit numeric filed in YYMMDD format..."

Does that mean that Jan 5th 2012 will be represented in your STCLDT as 120105?

If yes, Why are you looking for it to be less than 1300? Only a non-closed store (date is null (or 0)) will be less than 1300.

Otherwise, try this  if(Close_Date = 0 OR Close_Date  > Today(),'N','Y')

[jduenyas]

Correction :

 if(Close_Date = 0 OR Close_Date  > (Today()+7),'N','Y')

That will give you a cutoff of a week before the date becomes effective for the store to be noted as closed.

[has75042]

Thanks for your reply. 

if the store closed in April of 2004, since date the is in numeric field (40406), this number is less than today's date numeric field 192401, with this logic it is showing as open store.

[dplr-rn]

So you dont have a day there. If so You will check the length of the field and make the full date

[has75042]

Yes, I do have a day there.
2004-04-06.

[dplr-rn]

Not sure what the problem is then. is the format different?
you will need to play around with soem if statements and achieve what you need