Date function problems with dates from core not recognized as dates

[Former Member]

Our core provides dates in two formats: YYYYDDD and MMDDYY (YYYYDDD for today [2/21/12] would be 2012052). We have determined that Crystal Reports is not viewing them as dates, but rather numbers. If querying on just a specific date, no problem. But if I want to use a date function like DayOfWeek, then things don't work.

I need a report to always provide yesterday's data - except on Monday, where it needs to look back at Friday.

I tried a criteria of:

if DayOfWeek(CurrentDate) = 1 then {DDMAST.DATOP7} = CurrentDate - 2 else

if DayOfWeek(CurrentDate) = 2 then {DDMAST.DATOP7} = CurrentDate - 3 else

{DDMAST.DATOP7} = CurrentDate -1

{DDMAST.DATOP7} is the date field from our core.

I tried this but got an error message of "A Number is Required Here" and it highlights the CurrentDate - 2.

Since we believe that Crystal Report is not viewing our dates as dates - but viewing them as numbers, what do I need to do to correct this? Someone suggested I'd need to extract each date portion (month, date, and year) and create variables to "build" a date that Crystal Reports would understand. Any suggestions?

[abhilash_kumar]

Hi,

You could use the cdate function to convert the number field to date. Something like this:

if DayOfWeek(CurrentDate) = 1 then cdate({DDMAST.DATOP7}) = CurrentDate - 2 else
if DayOfWeek(CurrentDate) = 2 then cdate({DDMAST.DATOP7}) = CurrentDate - 3 else
cdate({DDMAST.DATOP7}) = CurrentDate -1

Let me know how this goes!

-Abhilash

[Former Member]

I tried that just now and it didn't work. Since the formula , as written, would have looked at February 20 and our offices were closed for President's Day, I altered the last line of the formula to be -4 and not -1 so it would look back to Friday February 17. Using the lines you gave me, the query churned on, preparing to display over 50,000+ results before I shut it down. The correct level of records should have been in the high 20's.

[Former Member]

Correction to the above, it returned zero records.

[abhilash_kumar]

Alright, could you confirm if Feb 20 looks this in your database - '2012052'?

cdate actually converts a number to date assuming the 1st 4 numbers make the year, the next two make the month and next two the day. In your example '2012052' does not make a complete date and it returns '10/22/7408' as the date when I convert it to date.

Am I missing something here?

-Abhilash

[Former Member]

We have two date formats available. Format "six" is MMDDYY so that February 21, 2012 would be 22112 (since I believe it drops the leading zero).

Our other option is fomat "seven" which is YYYYDDD. Here, February 21, 2012 would be 2012052 - since February 21st is the 52nd day of the year. There is no month indicator in this format. The DDD is the number of days into the year. To be clear, December 31, 2012 would be 2012365 since December 31st is the 365th day of the year.

[Former Member]

perhaps you could build multiple formulas, first formula would determine the length of your date field, once

you have that determined, you could use left, mid, and right string functions to break the date apart, and then

put it back together in a format CR can recognize. here is an example formula, although it does not fit your

situation, it may give you some ideas.

*numbervar JulianDate:=107.157;stringvar DateString;dateTimevar NewDate;

DateString := ToText(JulianDate,3);NewDate:=Date (2000+ToNumber(Mid(DateString,2,2)),1 ,1 );NewDate:=DateAdd("d", ToNumber(Mid(DateString,5, Length(DateString)-4))-1, NewDate);*