|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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) Show quote "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). Show quote "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 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...... >> >> > > > This won't work though, because @_In_theDateTime might be less than So why not pass in JUST THE DATE?> MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the > latter 1pm). 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. Okay I get it now - yes this will work. Thanks.
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23vvTFhfjFHA.3608@TK2MSFTNGP12.phx.gbl... >> 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. "Robin Tucker" <idontwanttobespammedanymore@reallyidont.com> wrote in This is probably a really simple one:message news:dbo636$hgi$1$8302bc10@news.demon.co.uk... 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......  |
|||||||||||||||||||||||
Other interesting topics