Comparing date only?

This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....You don't want to apply expressions to both sides of the WHERE clause,
otherwise you will negate any indexes. How about (assuming your oddly named
variable is a DATETIME or, better yet, SMALLDATETIME):
WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
> This is probably a really simple one:
> Given a table with a DATETIME column, how can I do an = or <> comparison
> with just the date part?
> ie.
> SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
> SOMEFUNC(@._In_theDataTime)
> , where SOMEFUNC will just return the date part (ignoring the time part)
> of theDateTime.
>
> Thanks.....
>|||Okay, thanks (and yes, my stored proc names are odd, as I use:
@._In_somevar, @._Out_somevar, @._InOut_somevar). I find it easier to
distinguish between local vars and parameters just by prefixing parameters
with `_'. It's a personal thing (no guidelines here) - also, quite often I
write Data when I mean to write Date and visa versa - apologies as I'm
slightly dyslexic. Anyway:
WHERE MyTable.TheDateTime >= (@._In_theDateTime)
AND MyTable.TheDateTime < (@._In_theDateTime + 1)
This won't work though, because @._In_theDateTime might be less than
MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
latter 1pm). I was thinking I would be able to say "DATEPART(x) =
DATEPART(y)" or something. I don't have millions of rows (average around
20 - 40k rows in this table), so I don't mind too much about not using the
indexes (at least for now).
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyawDVfjFHA.3336@.tk2msftngp13.phx.gbl...
> You don't want to apply expressions to both sides of the WHERE clause,
> otherwise you will negate any indexes. How about (assuming your oddly
> named variable is a DATETIME or, better yet, SMALLDATETIME):
> WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
> AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
>
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
>|||> This won't work though, because @._In_theDateTime might be less than
> MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
> latter 1pm).
So why not pass in JUST THE DATE?
Or, in the stored procedure, say:
SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
-- this will set the time portion to midnight without any messy
casts/conversions.|||See CONVERT function in SQL Server Books Online. It offers you various
styles for date formatting. One of them (112) gets rid of time portion.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....|||Okay I get it now - yes this will work. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23vvTFhfjFHA.3608@.TK2MSFTNGP12.phx.gbl...
> So why not pass in JUST THE DATE?
> Or, in the stored procedure, say:
> SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
> -- this will set the time portion to midnight without any messy
> casts/conversions.
>sqlsql