Converting datea types

[Stuartp]

Our company has imported several thousand records into an SQL Server database. The problem is that the dates have come through in a less than ideal format, so for example the date 27 November 2002 has come through as 20021127, and this is stored as a Decimal data type.

I have found that in SQL using

convert(datetime , convert(varchar,yourfield), 103)

in the query will work fine and format the date properly.

However I want to pass the results of this query to a Crystal Report using Active Data. If I point my report at this query to create the report definition it tells me there is an error converting the data type. If I leave it then I get dates looking like 20021127 in my report.

Is there a way either to make Crystal accept that the SQL query is correct, or to format this number as a proper date i.e. 27/11/2002

Many thanks

Stuart

 

[GJParker]

If the imported data is static, would it not be more advantageous to convert your raw data to the correct data type.

e.g. create a new column in your database and update the data ausing an sql statement

update MyTable
Set NewDate = convert(datetime , convert(varchar,OldDate), 103)

Just an Idea Gary Parker
Systems Support Analyst

 

[Stuartp]

Thanks - I have asked the database admin and he says we cannot convert the dates this way they have to be left in the current format for use in other ways.

So the conversion has to happen in Crystal somehow, or make Crystal recognise the SQL conversion query as valid.

Thanks

Stuart

 

[GJParker]

Which Version of Crystal reports are you using ?

In Crystal 9 You Can Create an SQL expression field to do the conversion, suing yuor same SQL expression

convert(datetime , convert(varchar,yourfield), 103) Gary Parker
Systems Support Analyst

 

[Stuartp]

I am using Crystal 8.5 - trying the same SQL expression there causes a data conversion error.

Stuart

 

[sbdt]

Make a formula in your Crystal Report
Use Additional Functions
MacolaNumberToDate(MacolaDate)
This is a special function for users of the Macola ERP system. Macola stores dates as a number, 20021127.
Your output will be 11/27/2002.

 

[badstink]

You can create a database view that looks much like the original table but has 1 extra column with the converted date value that was suggested by GJParker. Then write your report to query the view instead of the table.