DateDiff Crystal SQL Command

[Former Member]

Hi, is it possible to use DateDiff in a Crystal SQL Command?

I have tried this:

(last_date_of_service is a field in my database)

Select ...., DateDiff("d", last_date_of_service, Date) As "DAYS"

From mytable as "mt"

I get this error <A term expected, beginning with either of: udentifier, constant, aggregate, $$, (, :, +..... or %Upper^Sele [Database Vendor Code:12]

[Former Member · Accepted solution]

Ok, I figured out for some reason is doesn't like 'Date' for todays date or 'CurrentDate', but it will work if I use '2015-11-03'

[former_member292966 · Accepted solution]

Hi Nate,

The syntax you use in a SQL Command depends on your database.  For example SQL Server would use GetDate(). 

Thanks,

Brian

[DellSC · Accepted solution]

You also have to use the DateDiff function that is valid for your database, not the one from Crystal.  So, if you have SQL Server, the syntax would be something like this:

DateDiff(day, last_date_of_service, GetDate())

Note that there are no quotes around "day".

-Dell