convert to Date type with SQL Expression

[SusanStoHelit]

I have a similar quesiton as thread767-1300499

I need to convert this crystal XI formula:
dateadd("s",{table.seconds},datetime(1970,1,1,0,0,0))

to a SQL expression so that it is passed to the database. The report runs too slowly otherwise.

I tried:
dateadd("dd",(table.seconds/3600/24),date(1970,01,01))with no luck.

any ideas?

 

[lbass]

Please use something like:

{table.date} >= dateadd("s",{table.seconds},datetime(1970,1,1,0,0,0))

Then go to database->show SQL query. I believe you will find that it does pass to the database. I just tested it and it did.

-LB

 

[SusanStoHelit]

thanks LBass

it will need to be selected based on a parameter, so it actually looks more like this:


dateadd("s",{table.seconds},datetime(1970,1,1,0,0,0))>={?Download Start Date}

I tried that and it did not show in the SQL Query

 

[lbass]

I did test it and it worked, but I can't seem to recreate it today.

The way you set up the SQL expression depends upon your datasource/connectivity. If you were using Oracle, the following SQL expression works:

to_date('1-Jan-1970')+ "table"."seconds"/86400

If Access-based, the following works:

dateadd('s',table.`seconds`,'1970-1-1 00:00:00')

You would then compare the SQL expression with the parameter in your record selection formula as in:

{%date} >= {?date}

-LB