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?

[former_member292966]

Hi,

It's great that the different formats are distinguishable because that makes our life easier. Try this:

StringVar myDate; 

myDate := ToText ({DDMAST.DATOP7}, 0, "", ""); 

Select Length (myDate)
    Case 6: Date (2000 + ToNumber (myDate [5 to 6]), ToNumber (myDate [1 to 2]), ToNumber (myDate [3 to 4])) 
    Case 7: Date (ToNumber (myDate[1 to 4]), 1, 1) + (ToNumber (myDate [5 to 7]) - 1) 
    Default: Date (0, 0, 0);

What happens here is I converted your numeric dates to a string.

Checking the length of the string, I either parsed out the date parts or I calculated the number of days from the January 1 of the year you want.

I would recommend your formula check for null or 0 dates as well.

Good luck,

Brian

[Former Member]

I tried the date thing and the syntax killed me. I have this code:

...

stringVar myYear := brthDate [1 to 4];

stringVar myMonth := brthDate [5 to 6];

stringVar myDay := brthDate [7 to 8];

if (myYear = "") or (myYear = " ") then

myYear := "2099";

if (myMonth = "") or (myMonth = " ") then

myMonth := "01";

if (myDay = "") or (myDay = " ") then

myMonth := "31";

numberVar monthNum := ToNumber(myMonth);

//stringVar alertMsg1 := "monthNum = ";

//stringVar alertMsg2 := CStr(monthNum);

if (monthNum < 13 and monthNum > 0) then

stringVar tstMonth := MonthName(monthNum);

//alertMsg + " " + tstMonth;

StringVar txtHold := tstMonth + " " + myMonth + "/" + myDay + "/" + myYear;

dateVar myDate := date(ToNumber(myYear), ToNumber(myMonth), ToNumber(myDay));

CDate(myDate) + " " + txtHold;

I got an alert box saying "A date-time is required here" with "myDate" highlighted.

[former_member292966]

Hi Jerry,

How are you defining brthDate? It's a string variable but I'd like to see how you are building it. I would recommend you leave it as a date instead of converting to a string. This minimizes the extra conversion to number and back to a date.

Let's try this:

NumberVar myYear;
NumberVar myMonth;
NumberVar  myDay;

If IsDate (brthDate) Then 
   (myYear := ToNumber (brthDate [1 to 4]));
    myMonth := ToNumber (brthDate [5 to 6]);
    myDay := ToNumber (brthDate [7 to 8]);)
Else 
   (myYear := 2099;
   myMonth := 1; 
   myMonth := 31;); 

ToText (Date (myYear, myMonth, myDay), "yyyy/MM/dd") & " " & txtHold;

The error was because you are trying to concatenate a Date into a String. Before you do that the Date has to be converted to a String first. The result of a forumla must be the same type.

The biggest issue I believe you have it making sure brthDate is actually a date first. I used the IsDate function to verify this.

You will also notice I changed your StringVars to NumberVars to remove unncecessary conversions.

The last line just takes the results of your date parts, converts them to a date with the Date function and wrapped inside a ToText function that formats the date into a string.

Hope this helps,

Brian

[Former Member]

I think this is still being tackled incorrectly. The date is not YYYYMMDD where March 6 2012 would be 20120306. The date in our system is YYYYDDD where (this being leap year, making today the 66th day of the year) our YYYYDDD format would be 2012066. From what I can see in your work above, it looks like you are working on the idea that the month characters/numbers are the 5th and 6th in from the left. In our YYYYDDD format, there is no month indicator (and no day of the month) - ONLY number of days into the year. Our other date foormat is MMDDYY. Crystal reports is recognizing both of these as numbers and not dates.

[former_member292966]

Hi,

You're correct. There appears to be two branches in this thread.

To address your issue, Crystal only recognizes actual date types, ie. Months, Days and Years. Your serial date isn't a recognized date type so we have to:

1) Determine which format your date field is

2) Convert your number to a date using the appropriate formatting.

I am assuming your dates will either be 6 (MMddyy) or 7 (yyyyddd) characters long. If there are other patterns you'll need to determine that.

The problem I found is what does your date for today (March 6, 2012) look like in MMddyy format?

Any month before October would never have a leading 0 if your date is stored as a number. So you should take that into account as well using my logic.

I added that into the logic by checking for a length of 5.

NumberVar Full := 030612; 

StringVar myDate; 
 
myDate := ToText (Full, 0, "", ""); 
 
Select Length (myDate)
    Case 5: Date (2000 + ToNumber (myDate [4 to 5]), ToNumber (myDate [1]), ToNumber (myDate [2 to 3])) 
    Case 6: Date (2000 + ToNumber (myDate [5 to 6]), ToNumber (myDate [1 to 2]), ToNumber (myDate [3 to 4])) 
    Case 7: Date (ToNumber (myDate[1 to 4]), 1, 1) + (ToNumber (myDate [5 to 7]) - 1) 
    Default: Date (0, 0, 0);

If you edit the first line and change it to:

NumberVar Full := 2012066;

You will also get today's date.

[Former Member]

Sorry to all for not including the full information.

The data is stored as a string "yyyymmdd" and I want to convert it to an actual date field so various date functions can be used with it. In this particular case I'm just trying to turn it into a date and once that is done I can apply it to all the date fields that I need to.

Just to see the results I tried to create a date object then do a CDate to change it to a string and then concatenate it to the various pieces of the input date.

[former_member292966]

Hi,

That helps. Try this formula:

StringVar brthDate := "20120306"; 
DateVar myDate; 
 
If brthDate <> "" Then 
   myDate := Date (ToNumber (brthDate [1 to 4]), 
                    ToNumber (brthDate [5 to 6]), 
                    ToNumber (brthDate [7 to 8]))
Else 
   myDate := Date (2099, 1, 31); 
 
ToText (myDate, "yyyy/MM/dd");

I've simplified it a bit more. I made the assumption your string will always be either 8 characters or empty.

This version should work a lot better.

Good luck,

Brian

[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);*