Date paramaters in a Command with DateTime fields

Sunday 2 August 2009 @ 10:54 pm

I found some clever SQL that allows you to strip the Time off of a DateTime in a SQL Server Command.  This has several uses but it makes it simple to use normal Date parameters with DateTime fields and not having to worry about losing the last day due to the times being after midnight.    The SQL would look like this:

DateAdd( day, datediff( day , 0 , YourDateTimeValue) , 0)

Alternately you could use this:

Convert(date, YourDateTimeValue ))

This link describes several other methods to do the same thing.  Some might work better than others in your environment.

If you need some guidance when it comes to using SQL expressions or commands in your reports, you should get my Expert’t Guide to SQL Expressions, Options and Commands.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server