Integer to Date

[gkrenton]

I have a data field that is recorded in my DB as a integer that
actually reflects a date in a YYYY/MM format -
IE Feb 2004 shows as 200,402.
I need to convert this field to a date format so I can do some date calculations on it.

cddate({po_month}) gives me a date as calculated as if it was mm/yyyy -

I'm sure someone has already figured out how to work around this & my brain isn't seeing how to make it work.

Thanks in advance.
Gina

 

[synapsevampire]

Try:

whileprintingrecords;
stringvar DateStr:=totext({table.dateint},0,""
cdate(val(left(DateStr,4)),val(mid(DateStr,5,2)),1)

This will get you the first of the month.

-k

 

[gkrenton]

Thanks but I'm getting an 'error' telling me that the month number must be between 1 & 12 - I'm sort of thinking that this a 'data' issue on my end vs your formula. So then thought about adding an if >statement but still get this error.
Any suggestions?

Gina

 

[synapsevampire]

Try:

whileprintingrecords;
stringvar DateStr:=totext({table.dateint},0,""
if len(datestr) = 6 then
cdate(val(left(DateStr,4)),val(mid(DateStr,5,2)),1)
else
currentdate

You should get today's date for the bad ones, then just place the {table.dateint} field alongside to see what's in the field for the currentdate ones.

-k

 

[gkrenton]

Thanks that seems to handle it & found where the data is messed up to get that handled as well.

-Gina