Date poition comparison of a datetime field

[colinhumber]

I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')" .

What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.

Is there any way to only compare the date portions of both the SQL
field and the passed in value?

Thanks.

[Hugo Kornelis]

On 16 May 2005 07:54:28 -0700, colinhumber wrote:

I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')" .

What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.

Is there any way to only compare the date portions of both the SQL
field and the passed in value?

Thanks.

Hi colinhumber,

There are several ways to strip the time portion from a datetime column
or variable. My favorite is

DATEADD(day,
DATEDIFF(day, '20050101', datecol),
'20050101')

Note that the choice of '20050101' is purely arbitrary; the same formula
will work with any date constant (or even with an integer constant; some
people use 0, which is implicitly converted to 1900/01/01.

If both the datetime column in your data and the passed value may have a
time portion, you could use the above formula for both. But if there's
an index on the column, then it's better not to use the column as part
of the formula. You could use this little trick:

DECLARE @start datetime, @end datetime
SET @start = DATEADD(day,
DATEDIFF(day, '20050101', @PassedDate),
'20050101')
SET @end = DATEADD(day, 1, @start)
SELECT ???
FROM ???
WHERE DateTimeColumn >= @start
AND DateTimeColumn < @End

For much more information about datetime data in SQL Server, check out
Tibor's page: http://www.karaszi.com/SQLServer/info_datetime.asp.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)