I haven’t written many posts on SQL topics. But recently it seems that much of my work involves writing reports based on complex SQL queries. Part of this involves converting Crystal formula logic into SQL syntax. The fun part is that the syntax varies from one flavor of SQL to another, especially for date calculations.

So today’s post is specific to SQL Server syntax, one of the most common flavors. I often need date calculations that are relative to today’s date. In a Crystal formula I would use CurrentDate. In SQL Server syntax the closest equivalent function is GetDate(). I have used it for quite a while assuming that it is the same as CurrentDate. But I just recently discovered that GetDate() includes both the date and the time, which changes things. If you run a report at 2pm on March 8th and the WHERE clause says:

WHERE orders.Date >= GetDate() -2

you might expect to get all the records on March 6th, but you probably wouldn’t. If your Orders.Date field doesn’t store times you would not get any records from the 6th. If that field does have times you would get records, but only those after 2pm on the 6th. So if you want the calculation above to behave like the CurrentDate function in Crystal you have to remove the time from GetDate().

I found two ways to strip off the time off any DateTime value. The one I see listed most often is this one:

DateDiff(d, 0, GetDate())

or this one submitted by Ralph Wahlert:

Cast(GetDate() as Date)

These works great in a WHERE or ON clause, but have one flaw. If you include them in your SELECT clause so that you can show the date on the report the first will appear as a number in Crystal and the second will appear as a string. To use them in the SELECT and have them as dates, you have to convert them to DateTimes:

cast(DateDiff(d, 0, GetDate()) as DateTime)
cast(cast(GetDate() as Date) as DateTime)

These two work in the WHERE/ON clauses as well as the SELECT. In each case they will return a DateTime value but with the time portion set to 12:00am.