Convert string field to date 1

[MacolaHelp]

I have encountered a db that stores dates as a string in YYYYMMDD order. Is there a formula to convert this string to MM/DD/YYYY format? Thanks in advance for your help.

 

[kenhamady]

The following will create a true date value that you can format as you please.

Date (
Val ({Field} [1 to 4 ]),
Val ({Field} [5 to 6 ]),
Val ({Field} [7 to 8 ]) )

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[bdreed35]

You can use this formula to convert the string value to a date value:

Date(ToNumber({String_Date.stringDate}[1 to 4]),ToNumber({String_Date.stringDate}[5 to 6]),ToNumber({String_Date.stringDate}[7 to 8]))

When you place the above formula on the report, format it (Right Click, Format Field, Date/Time Tab, highlight "03/01/1999&quot to the style you want.

~Brian

 

[synapsevampire]

You might also consider creating a SQL Expression to cast/convert it to a date on the server side.

-k

 

[MacolaHelp]

Thanks for the help!

 

[kenhamady]

IF this is for Macola I think the dates are numeric, not string. You might need the formula on my web site's FORMULAs page.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[MacolaHelp]

The number to date works fine for macola & I use it in reports against the macola db. The problem I had today was in a shipping system db that stores the date as a string & your formula worked just fine.