Convert YYYYddd formatted String to Date? 3

[NickNiti]

I have a faircom C-TREE database which is accessed via ODBC by crystal. The date field for this particular database is YYYYddd where ddd is the day of the YEAR. FOr example:
January 1, 2003 ---> 2003001
December 31, 2003 ---> 2003365

I need to convert this string into a crystal date so that I can format it for human consumption (MM/DD/YYYY).

I would also prefer to do this as a crystal function instead of SQL (the load on the faircom serve ris high)

Any help would be greatly appreciated

 

[Ngolem]

You should read the following CR KnowledgeBase artical on Julian dates

http://support.crystaldecisions.com/library/kbase/articles/c2009191.asp

Once you separate the julian day value

tonumber(right({Table.datestring},3))

You should be able to use a formula downloaded from Crystal to solve your problem...sorry but I don't have these formulas to test but it should be straight forward

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bdreed35]

Here is a formula that will convert your date to MM/DD/YYYY format:

* * *Replace {table.date} with your string date field* * *

numberVar YearNum   := Val(Left({table.date},4));
numberVar NumDays   := Val(Right({table.date},3));
numberVar CountOfPreviousDays := 0;
dateVar   returnDate ;

numberVar MonthNum;
numberVar FirstDay;
numberVar LastDay;

for MonthNum := 1 to 12 do 
(
    FirstDay := CountOfPreviousDays + 1;
    LastDay  := CountOfPreviousDays + Day((DateAdd("m",1,Date(YearNum,MonthNum,1))-1));

    if NumDays IN [FirstDay to LastDay] then
        (
            returnDate := DATE(YearNum, MonthNum, (NumDays - CountOfPreviousDays));
            exit for;
        );
    CountOfPreviousDays := CountOfPreviousDays + (LastDay - FirstDay) + 1;
);

returnDate;

~Brian

 

[NickNiti]

Brian....Thanks for the suggestion...But when I try your code I get
"A String Is Required Here"
numberVar YearNum := Val(Left({rx.FillDate},4));
In this line....


?

 

[Ngolem]

Actually the post by bdreed35 caused me to think of an even easier formula.

By default...add a number to a date is adding days so this is the formula I would use

//@ConvertDate
WhilePrintingRecords;
StringVar result := "";
numberVar tempY := tonumber(Left({Table.DateString},4));
numberVar tempD := tonumber(right({Table.DateString},3));

result := totext(Date(tempY,12,31)+ tempD,"MM/dd/yyyy"

result;

Short and sweet...think that will work for you.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Ngolem]

So your Date is not a string but rather a number...you should tell us that

My formula would change to this them

//@ConvertDate
WhilePrintingRecords;
StringVar result := "";
numberVar tempY := tonumber(Left(totext({Table.DateString},0,"","&quot,4));
numberVar tempD := tonumber(right(totext({Table.DateString},0,"","&quot,3));

result := totext(Date(tempY,12,31)+ tempD,"MM/dd/yyyy"

result;


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bdreed35]

Nice job Jim*. I certainly over complicated it a bit.

One thing: I think that that you need to subtract 1 from tempY to get the right answer since you would want to start off from the last day of the previous Year before you add the days.

result := totext(Date((tempY - 1),12,31)+ tempD,"MM/dd/yyyy");

~Brian

 

[Ngolem]

yes...exactly....good catch

Between the two of us we got it

A star for you as well

for our poster here is the NGolem-bdreed35 collaborated result

//@ConvertDate
WhilePrintingRecords;
StringVar result := "";
numberVar tempY := tonumber(Left(totext({Table.DateString},0,"","&quot,4));
numberVar tempD := tonumber(right(totext({Table.DateString},0,"","&quot,3));

result := totext(Date((tempY - 1),12,31)+ tempD,"MM/dd/yyyy"

result;


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[NickNiti]

Great Guys!

It works perfect...Sorry about the confusion with STrings and Numbers...I thought that the field was a string....

Your streamlined formula is also very quick....

Much Thanks!

 

[mbarron]

Just because I like to play with formulas:

numbervar dnum:={date}/1000;
numbervar yr:=truncate(dnum);
numbervar dy:=(dnum-yr)*1000;
date(dateadd("d",dy-1,date(yr,1,1)))

or end it with the following if you want a string result instead of a date.

totext(date(dateadd("d",dy-1,date(yr,1,1))),"MM/dd/YYYY&quot


Jim's fomula could also end like this:
result := totext(Date(tempY,1,1)+ tempD -1,"MM/dd/yyyy"

It subtracts one day from the day count instead of adding the day count to 12,31 of the previous year.



Mike

 

[dgillz]

Lets make this simple. Download the NumberToDate() function at the following link:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufltdate.exe.asp

You can take an 8 digit integer in a YYYYMMDD format and convert it to a real date with this function. For example:

Numbertodate(20031105)

Would return the date 11/05/2003.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Ngolem]

dgillz - that was not his problem...his "date" was not in the form of a date...it was a 7 digit number with the first 4 numbers relating to the year and the last 4 numbers relating to number of days from Dec/31 of the previous year....your formula would not work

Mbarron - not much different in complexity...just different functions is all.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[dgillz]

My apologies I misread the post.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[mbarron]

I know. I was just offering a different perspective.


Mike