Convert integer field YYYYMMDD to date

[ladyemrys]

Hi!

I'm working with SQL 2008 and CR XI. I have a field in the database that is supposed to be a date field, but it is held as a number - for example: 20100323. I need the report to have real date parameters so I need to convert it to a date. I have tried CDate({TKPay.LDate}), CDateTime and Date and none of them worked. Any advice is much appreciated!

Thanks!
LE

 

[FireGeek21]

How about for a mm//dd/yyyy format you use:

MID({TKPay.LDate}, 5, 2) & "/" &
RIGHT({TKPay.LDate},2) & "/" &
LEFT({TKPay.LDate},4)

Hope this helps!


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[ladyemrys]

Thanks RSGeek!

It is looking for a string value with that formula and my field is an integer/number field. Any other ideas?

Thanks again!

 

[ladyemrys]

Thanks for your help! I got it to work just using the numbers with my parameters.

 

[FireGeek21]

You could use TOTEXT( ) to convert to a string.


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)