SQL Expression

[ShortyA]

Hi,
I have a date field that is stored as the number of days since 30/12/1899 (I have no idea why). Although I can use the DateTimeDiff Crystal function I would like to convert this using an SQL Expression so I can pass more down to the server.

Does anyone have any suggestions as to how this can be achieved ?

I am running CR9 Professional under W2k.
Many thanks - ShortyA

 

[bdreed35]

Since you really didn't specify how you are using it in your Record Selection Criteria, its hard to give you a specific example.

You can always convert 30/12/1899 to a date field and then add the field that holds the number of days and then do your comparison. This example will get passed down in the SQL Query:

{Orders.Order Date} > CDate(1899,12,30) + {table.numberofdays}

This doesn't use a SQL Expression. It is better to not use a SQL Expression field if you can as it adds another layer of processing.

~Brian

 

[ShortyA]

Brian,
thanks for your response. I would like to use it in the record selection to reduce the volume of records that are returned (we have several million per table and the tables are then linked). The user can input a date parameter eg 22/01/2004 and this could be used in the record sel.

My understanding of SQL expression was that if these were used then more processing could be passed to the server.
ShortyA

 

[bdreed35]

It is true that SQL Expressions will allow you to pass more processing to the server. If you can find an alternate route, without using the SQL Expression, and get your selection criteria passed down, this is the best solution. If this cannot be achieved, the next best this would be to create a SQL Expression field so that you can get that criteria passed down to the database.

The solution I presented will allow you to get the criteria passed down to the database without having to use a SQL Expression field.

{?Date_Parameter} > CDate(1899,12,30) + (table.number_of_days}

Add this to your Record Selection Criteria.
Replace {?Date_Parameter} with your Date parameter.
Replace (table.number_of_days} with the field in your database that holds the number of days to add to 30/12/1899.
Run your report after that.
Now go to Report, Show SQL Query.
You should see the selection criteria in your Where clause.

~Brian

 

[ShortyA]

Thanks Brian - I will give it a whirl!
Alec

 

[lyanch]

Not sure that it will help, but I found it more reliable passed to the server if I put everything that would calculate down to a single number on oneside of the formula.

In this case

datediff("d",date(1899,12,30),{?Date_Parameter}) > {table.numberofdays}


the datediff calculates to a constant sent directly through the SQL. I have the same set up only seconds since Jan 01, 1970. I use this type of conversion all the time and have never had problems with it passing.

Lisa

 

[ShortyA]

Lisa,
makes sense and I am all for passing as much as possible to the database server.
Thanks - Alec