Date Conversion Problem 1

[toict]

looking for a a way to convert the date format from 7 digits to standard format ie: 2,003,137 to ?/?/2003.

Any help would be appreciated.
Thank you

 

[budbeth]

Mid([datefieldname],5,2) & "/" & Mid([datefieldname],7,2) & "/" & Mid([datefieldname],3,2))

HTH

Beth L

 

[toict]

This would probably work fine if the the input was a string field however the input is numeric.

The field is orderdate for merchandise and I am trying to compare the date to the results of this Date calculation which is when the order was actually filled.

DateValue({filled.date}/(24*60*60)+DateDiff("d",#12/30/1899#,#01/01/1970#))

this field is a 10 digit numeric ie; 1,072,723,273

 

[synapsevampire]

A date format wouldn't be 7 chars at the minimum nor maximum, since you need 8 to show a date. If it's stored as such, you're in deep kim chee as a date such as:

11/1/2003 could be interpreted different ways when stored as:

2,003,111

Is it the 1st of November or the 11th of January?

Sounds like you are either dealing with a bad date format, or you don't understand the data.

Right click the field and select browse data, paste it somewhere, and then copy and post it here.

Note that Beth's formula assumes an 8 digit precision, which makes sense.

-k

 

[toict]

It is definately being stored as 7 digits.

the date is the year and day of the year it was ordered

2003335 -> 12/01/2003
2003344 -> 12/10/2003
2003336 -> 12/02/2003
2003336 -> 12/02/2003
2003336 -> 12/02/2003
2003336 -> 12/02/2003
2003337 -> 12/03/2003

Thanks again in advance

 

[budbeth]

From the Crystal Support web site Article ID c2001866:


Synopsis

How can a whole number, like 98268.00, be converted to a date, where 98 is the year and 268 is the number of days since January 1st?


Solution

Create a formula that formats the field as a 5 digit string, passes it to a string variable, and then parses out the first 2 digits (for the year) into a number variable.

See the formula below which uses the date function to create a January 1st date with the year number variable, and adds the number of days less 1 to that January 1st date:

In the example below, {field} is the number in the database that represents the date, i.e. 98268.

NumberVar Year1;
StringVar Date1;

Date1 :=ToText({field}, "00000"
Year1 :=ToNumber(Date1[1 to 2])+1900;

Date (Year1,01,01) + (ToNumber(Date1[3 to 5]) - 1);

In this particular example (field = 98268), the date returned will be September 25, 1998.

 

[synapsevampire]

I see, I wondered if it was that momentarily...

Try:

cdate(val(left({table.date},4)),1,1)+val(mid({table.date},5,3))

-k

 

[toict]

For some reason it is still telling me a string is required here.

cdate(val(left(?{table.date},4)),1,1)+val(mid({table.date},5,3))

 

[toict]

budbeth,

Thank you for your help I was able to modify your suggestion to handle the the extra digits and the results are perfect.

NumberVar Year1;
StringVar Date1;

Date1 :=ToText({Filled.Date}, "0000000"
Year1 :=ToNumber(Date1[1 to 4]);

Date (Year1,01,01) + (ToNumber(Date1[5 to 7]) - 1);


Thanks Again.

 

[synapsevampire]

Sorry, had a typo of a ? in there:

cdate(val(left({table.date},4)),1,1)+val(mid({table.date},5,3))

-k

 

[budbeth]

Glad I was able to help.

Just an FYI -- I went back to the Crystal Decisions web site to look something else up & found out it's down as they convert it for the new company, Business Objects, that bought out Crystal.

Beth L

 

[synapsevampire]

Yeah, the merger was about a month ago, Beth.

Who knows what the future holds now, but there have been a few posts here regarding it, mostly speculation and nervousness.

-k

 

[budbeth]

Yeah, I knew the merger was coming a awhile back.
I was on their Crystal Decisions site one day and the next -- less than 24 hours later -- it was being switched.
If we're lucky they will continue the Knowledge base...

Beth L