Brain buster...date formular that eludes me.

[davefm1]

Hi, im looking for away to change a char vaible that sent to me as a string formatof either yymmdd or yyyymmdd to

example 860410 to date field like 10 APR 1986 with no dashs. Ive try the cystal date functions in formula section and nothing seems to work any one no of a way to doing this in ethier crystal context or vb context. I am using crysal reports version 8.

Thanks
Dave

 

[Ngolem]

is that you want a date field value or that you want to see the string variable converted to another format...
ie: 10 APR 1986

I will assume that you want a true date value...since you can always format the date field to display the value anyway you want.

the only tricky part here relates to 6 character dates...what century is a 2 character date?

my approach would be that a 2 digit date > 25 is 1925 or greater...less than that is for the year 2000 - 2024
Unless of course you expect dates to be lower that 1925...this will give a report that would last 20 years before requiring maintenance (unless dates can go beyond 2024 for budget planning or something like that)

so this is the formula I'd use

if length({Table.dateString}) = 6 then
(
if tonumber({Table.dateString}[1 to 2]) >= 25 then
date(tonumber({Table.dateString}[1 to 2] + 1900),
tonumber({Table.dateString}[3 to 4]),
tonumber({Table.dateString}[5 to 6]))
else
date(tonumber({Table.dateString}[1 to 2] + 2000),
tonumber({Table.dateString}[3 to 4]),
tonumber({Table.dateString}[5 to 6]));
)
else if length({Table.dateString}) = 8 then
date(tonumber({Table.dateString}[1 to 4]),
tonumber({Table.dateString}[5 to 6]),
tonumber({Table.dateString}[7 to 8]));

i could add tests to see that the characters were all numeric but I don't think that is necessary. If the string is something other than 6 or 8 characters it will just fall through and leave a null.

Now in the date/time tab of this field...select the appropriate format...to eliminate the dashes just put a single blank space in the separators.

hope this helps
Jim Broadbent

 

[dgillz]

Keep it simple, go to the crystal decisions website and download NumbertoDate(), a UFL which converts and 8 digit integer in a YYYYMMDD format into a real date.

If they are really strings, you will have to convert them to numbers first. Also if you have a 6 digit field, you will need to build some logic as Jim suggested to determine when to add either 19000000 or 20000000 to the 6 digit number to get a value that numbertodate() can work with.

Send me an email and I can send you the numbertodate() UFL if you are interested. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[MrBillSC]

Hi Dave,

I am assuming you are able to convert the string date into a date formula field using the date function. If not let me know.

Displaying the date as 10 Apr 2002 is just a matter of formatting the field on the report. Try the following:
Right click on the field on the report and select Format Field...

In the Format Editor, click the Customize button.
In the Format section of the Custom Style box under month select "Mar". Under day select "1". Under year select 1999.

In the Order section select DMY.

In the Seperator section replace the default "/" with a space.

The sample box at the bottom of the screen will show you how your custom format looks.

Good luck.

bill

 

[davefm1]

Hi Jim;
I try your date function below just change the 25 to 50 for century check and added double qoutes around the century "1900"and "2000" and test formula says no error's However, I recieved a message stating date fields must be between year 1 and year 9999

if length({rpt_53_get_all_data.item_bdt}) = 6 then
(
if tonumber({rpt_53_get_all_data.item_bdt}[1 to 2]) >= 50 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2] + 1900),
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]))
else
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2] + 2000),
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]));
)
else if length({rpt_53_get_all_data.item_bdt}) = 8 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]),
tonumber({rpt_53_get_all_data.item_bdt}[7 to 8]));

I then change the date formula to the following by moving the porran to before the century and formilar checked it out ok . seems to work just fine.

if length({rpt_53_get_all_data.item_bdt}) = 6 then
(
if tonumber({rpt_53_get_all_data.item_bdt}[1 to 2]) >= 50 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2])+ 1900,
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]))
else
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2])+ 2000,
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]));
)
else if length({rpt_53_get_all_data.item_bdt}) = 8 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]),
tonumber({rpt_53_get_all_data.item_bdt}[7 to 8]));


Thanks Jim,

and

Thanks Bill
For space in cutomizeing date field

 

[Ngolem]

Sorry about that....got carried away with the brackets...glad you worked it out Jim Broadbent

 

[chelseatech]

Your string is almost like a DTS formated date, so with the Picture function you can convert it to DTS and then use the internal DTS function to get the date you want.

If length({Table.dateString}) =6 then
DTStoDate(picture({Table.dateString},"xx/xx/xx&quot)
else If length({Table.dateString}) =8 then
DTStoDate(picture({Table.dateString},"xxxx/xx/xx&quot)
else Date(0,0,0) // invalid date

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[davefm1]

Is There a way to formate Char date as a ture date and display data in that field that may not be char data date such as 'N/A'

The following formula returns me a true date format however when i try to display and data that's length may be less than than six as is example N/A it displays it but turns the date back to yymmdd or yyyymmdd formated which I just change and formated for date format 109 Mar 2002 can i do both inthge same formula or do I need to call a second formula to keep this from happening.

if length({rpt_53_get_all_data.item_bdt}) = 6 then
(
if tonumber({rpt_53_get_all_data.item_bdt}[1 to 2]) >= 50 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2])+ 1900,
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]))
else
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 2])+ 2000,
tonumber({rpt_53_get_all_data.item_bdt}[3 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]));
)
else if length({rpt_53_get_all_data.item_bdt}) = 8 then
date(tonumber({rpt_53_get_all_data.item_bdt}[1 to 4]),
tonumber({rpt_53_get_all_data.item_bdt}[5 to 6]),
tonumber({rpt_53_get_all_data.item_bdt}[7 to 8]));

//******* below is suppose to display any data that they may have entered other than a date like N/A.
//********however, it will display not only the N/A but return all dates back to yymmdd or yyyymmdd.

if length({rpt_53_get_all_data.item_bdt})<>6 or length({rpt_53_get_all_data.item_bdt})<>8 then
({rpt_53_get_all_data.item_bdt});

 

[dgillz]

Isdate() tests the data and returns true or false if it encounters a date, or a string convertanle to a date with the Cdate() or datevalue() functions.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com