SQL Get Current Date

[TopJack]

Is it possible to create an SQL command line for Crystal Reports 9 that automatically gets the current date.

In Crystal Report Designer I can create a selection formula that can look up the current date (today date). When I look at the SQL statement behind the selection formula it specifically states todays date, ie "TO_DATE ('18-01-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')" ... not very flexible.

Can I overwrite this selection formula with an automatic date rather than a declared specific date.

I wanted to use something like "GETDATE()" which is a general SQL statement to retrieve the current date. But unfortunately Crystal doesn't like the syntax.

Any ideas ? Thanks.

 

[synapsevampire]

When you look at the SLQ statement you're seeing what Crystal has converted the highly flexible, dynamic date to in whatever SQL you are using (appears to be SQL Server or Sybase). In general, post technical information. SQL is a generic database programming language, not a database.

GETDATE() is the same as currentdate or date in crystal.

You're overthinking this, Crystal has already doen the work for you, except that it is taking the date from wherever the Crystal Report is ran, not from the SQL Server.

-k

 

[TopJack]

Sorry I haven't explained myself very well.

I have a report that due to its complexities (not by choice) uses an "Add Command" SQL statement in order to efficiently and correctly retrieve data from many tables in an Oracle database. In order to significantly reduce the amount of data returned to the report I would need to reference todays date from where ever it runs, lets say for example my desktop pc, rather than return all the data through the "Add Command" and then filter the data in the usual manner, ie selection formula, after the data has been returned by the reports SQL - This significantly slows the report down.

My SQL statement is multiple lines with many UNIONS and also sub-queries inside the main statements. I can reference todays date manually inside this SQL and the report runs quick and correctly. The problem is I would need to edit the SQL everytime I run the report - not good when many others need to access the report aswell. If I could dynamically reference todays date inside the SQL everything would be "spot-on".

I need to take the smartness away from the Crystal Designer "CurrentDate" and put it directly in the SQL Command. I'm no expert on SQL (or Crystal for that matter) but there must a way to call the current date from the pc or server using the "Add Command" SQL technique.

Any comments appreciated, Thanks.

 

[synapsevampire]

No, you don't need to edit the SQL each time, use GETDATE in your SQL.

You could also create a parameter within the ADD COMMAND if you'd prefer to prompt the user for a date.

If I recall T-SQL syntax, it's something like:

select *
from table t
where t.date <= getdate()

Since you are primarily asking about SQL Server T-SQL syntax you'll find better resources on one of the SQL Server forums.

-k

 

[TopJack]

Thanks a lot for the help