Testing for TODAY in Add Command SQL query

[RenaG]

I am working in Crystal 10.

I have created an Add Command SQL query and need to include in the Where a test for today -365 (or the past year). Something like:

Appt_date <= today()-365

What function do you use to represent today's date? I tried sysdate and that didn't work.

TIA!
~RLG

 

[RenaG]

I figured it out:

 Appt_date <= getdate() - 365

~RLG

 

[RTag]

Just FYI: GetDate() will return date and time. So Appt_date <= getdate() - 365 will return everything before the same time one year ago and your report will show different data at 8:00AM and 3:00PM on the same day.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[TeyBrady]

If you are using a command object you will probably want to be using sysdate inside the command object and not outside in crystal. I think you are bringing back all records and making crystal filter them. Let the database do the work. You will probably want to use trunc(sysdate) to avoid issues with time like RTag mentioned.

 

[RenaG]

Good point RTag. I hadn't thought of that. And actually, I realized that I had my '<' pointing the wrong direction. I want everything WITHIN the last year so it is '>='.

TeyBrady - I tried your suggestion. This is what I coded:

App_DtTm >= trunc(sysdate)-365

When I clicked OK, I got this message:

Failed to open a rowset.
Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server] 'trunc' is not a recognized built-in function name.

So I took 'trunc' off:

App_DtTm >= sysdate-365

When I clicked OK, I got this message:

Failed to open a rowset.
Details: 42522:[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid column name 'sysdate'.

How to I strip off the time portion when I use GetDate()?

~RLG

 

[RTag]

I assume you are using SQLServer:
for SQLServer - any version
convert(datetime,floor(convert(decimal(19,5),Getdate())))

for SQLServer 2008 (may be 2005 too but not sure)
convert(date,Getdate())

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[TeyBrady]

I'm sorry I was thinking Oracle.