Convert a Text YYYYMMDD (20041228) to a Date 1

[Mr7Eleven]

I have a SQL database that stores the date as 20041228, and I would like Crystal to output 12/28/2004. I have tried for a couple of hours to figure it out, and can't - my first post here!

 

[Wacki]

Try putting the date into a formula and parsing it there.
Visual Baci Syntax
Dim Dat as String
Dim yr as string
Dim mn as String
dim da as String

Dat = ToText ({t_fcstOLIHeader.VT_Start})
yr = Mid(Dat,1,4)
mn = Mid(Dat,5,2)
da = Mid(Dat, 7 ,2)
formula = da + "/" + mn + "/" + yr

Wacki

 

[bdreed35]

This will convert it to a date, which will allow you to then format it however you want:

cdate(int({table.date}/10000),int(remainder({table.date},10000)/100),remainder(remainder({table.date},10000),100))

~Brian

 

[Madawc]

A formula field with
ToDate(Date({your.field}), "dd/MM/yyyy")
ought to do the same job more neatly.

Madawc Williams (East Anglia, UK)

 

[Mr7Eleven]

I tried the last option, and I put into the Formula Editor for my made up field 'UDF8_to_Date':

ToDate(Date({DETAIL_ITEM.ITEM_USERFIEL8}), "dd/MM/yyyy")

And Crystal returned this when I pushed X2:
'The remaining text does not appear to part of the formula'

Did I do something wrong? Please forgive - I am very, very new to all of this!

 

[Mr7Eleven]

I also tried the 2nd suggestion of "cdate(int({DETAIL_ITEM.ITEM_USERFIEL8}/10000),int(remainder({DETAIL_ITEM.ITEM_USERFIEL8},10000)/100),remainder(remainder({DETAIL_ITEM.ITEM_USERFIEL8},10000),100)) "

but Crystal returned this:
A Number, or currency amount is required here

Is that because Detail_Item.Item_Userfiel8 is a text field?

Still open to suggestions!

 

[bdreed35]

Madawc:

I can't get your formula to work either. I don't think the date function understands the numeric yyyymmdd format.

~Brian

 

[bdreed35]

Yes, mine failed because it is stored as text.

Give this a try:

cdate(int(val({DETAIL_ITEM.ITEM_USERFIEL8})/10000),int(remainder(val({DETAIL_ITEM.ITEM_USERFIEL8}),10000)/100),remainder(remainder(val({DETAIL_ITEM.ITEM_USERFIEL8}),10000),100))

~Brian

 

[towntopic]

Here's what I use.....

Date (ToNumber (Mid ({table.date},1 , 4)),
ToNumber (Mid ({table.date},5 , 2)),
ToNumber (Mid ({table.date},7 , 2))))

This of course is assuming that your field YYYYMMDD is a string.

 

[Mr7Eleven]

Brian's last suggestion worked for me, and that is awesome. I am new to this board and I will be reading and learning from here on out.

Thanks so much to those who offered help.

 

[towntopic]

If someone answers your original post, then please click on the Thanks so and so for this valuable post. This helps for knowing that a solution has been given.

Welcome to the board.

 

[dgillz]

Here's a better formula:

NumberToDate(ToNumber({YourField}))

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[stevefah]

That's a User Function Library (UFL), which uses ufltdate.dll. You can download it from our website at

http://support.businessobjects.com/search

by searching for the above EXE file.

If you don't have the UFL installed, it won't work.