Datetime Issue
-
Hi,
I am trying to pull out records from a table where the date is todays date. The date column recrods the date in this format: 06/10/2006 09:54:54
However, when I run my query which is somthing like the following:
SELECT * FROM Sales WHERE status = 8
AND (convert(varchar, ShipDate, 103) = convert(varchar,getdate(),103))
I used the convert to get rid of the time stamp but it still has not worked.
Can anyone tell me what I am doing wrong here?
Many thanks
Reet
-
`Try this
SELECT * FROM Sales WHERE status = 8
AND DATEADD(dd,DATEDIFF(dd,0,shipdate),0) = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
First check if status =8 is returning any rows without date filter.
Prasad Bhogadi
www.inforaise.com -
Hi Prasad,
Your solution worked a treated.
Thank you very much for your help!
Cheers
Reet.
-
Just a note folks... anytime you use a column name in a formula in the WHERE or ON clause of a SELECT, you make it impossible for the coveted INDEX SEEK to occur. At best, you'll get an INDEX SCAN (about 51 times slower is what personal experience claims).
Although the code is a bit longer, a better way to do this is as follows...
- SELECT * FROM Sales WHERE status = 8
AND shipdate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
- AND shipdate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)+1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions - SELECT * FROM Sales WHERE status = 8
-
Further information regarding the SARG issue can be found on this site, search for "Beware of Search Argument (SARG) Data Types"....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply