Sql query brings the datetime information as a string

[harryo28]

I have a simple SQL query that I copied from an existing report (no formulas) to SQL Designer 8.5. When I try to create a new report off this query the datetime data appears as a string. I tried to convert it to a datetime format but with no luck. I also tried to convert the datetime to datetime in the select statment but the same problem occurs. In both the query and report the datetime information appears as yyyy\mm\dd hh:mm:ss format but can not be used in formulas. Any suggestions? Thanks

 

[synapsevampire]

You shouyld first understand that the Crystal SQL Query designer is no longer supported in recent versions of Crystal, and was never a brilliant idea.

At any rate, you should post an actual datetime returned rather than trying to describe it...

You might be able to just use the datetime({table.field}) and then use the formula as your date field, but this is dependent upon what is actually being passed.

The alternative would be to use a formula something like:

Stringvar TheDate := {table.datestringfield};
stringvar array NewDate:= split(replace(replace(replace(TheDate," ",","),"/",","),":",","),",");
cdatetime(val(NewDate[1]),val(NewDate[2]),val(NewDate[3]),val(NewDate[4]),val(NewDate[5]),val(NewDate[6]))

If it doesn't work, it's probably because the format is different from what you posted.

-k

 

[Turkbear]

Hi,
The very first thing to check is the database ( what kind of database, by the way?)
Is that field really a DateTime ( or Date, Or Timestamp)?
Crystal usually is not smart enough to redefine a data type.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Ardechois]

I had this issue a week ago, the field was a date in the database, everything well defined and I had exactly the same issue. Crystal didn't want to recognize the date format, it was set as string.
So in the SQL, it forced the date to characters
to_char(ACT1.DATESTAMP,'DD/MM/YY HH24:MI:SS') DATESTAMP,

and It worked ! Crystal took my character string as a date! without any specifications!
Very very strange !

Tell me if it worked for you

 

[synapsevampire]

Not strange at all, depending upon your version of Crystal.

Using SQL Expressions is standard in CR 8.X, but again, the Query Designer is a bad idea, and it's no longer supported...

-k