December 29, 2015 at 9:04 am
Trying to match a.dateappreceived with todays date, code 1 works, 2 and 3 do not, cant figure out th reason:
WHERE (a.DateAppReceived Between GETDATE()-1 AND GETDATE())
WHERE (a.DateAppReceived = CONVERT(varchar(10),(DATEADD(dd, datediff(dd, 0, GETDATE()), 0)),101))
WHERE (a.DateAppReceived = CONVERT(varchar(10), GETDATE(), 101))
December 29, 2015 at 9:12 am
What is the data type of a.DateAppReceived?
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 29, 2015 at 9:35 am
DATETIME
SELECT STATEMENT
, CONVERT(varchar(10), a.DateAppReceived, 101) AS DateAppReceived
December 29, 2015 at 9:54 am
If you mean all times for today's date, and not all times for the immediately preceding 24 hours, then you should do this:
WHERE (a.DateAppReceived >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
a.DateAppReceived < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0))
Edit: CONVERT in the SELECT clause if fine, but you don't want to use it in the WHERE clause.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2015 at 10:17 am
SQLPain (12/29/2015)
Trying to match a.dateappreceived with todays date, code 1 works, 2 and 3 do not, cant figure out th reason:
WHERE (a.DateAppReceived Between GETDATE()-1 AND GETDATE())WHERE (a.DateAppReceived = CONVERT(varchar(10),(DATEADD(dd, datediff(dd, 0, GETDATE()), 0)),101))
WHERE (a.DateAppReceived = CONVERT(varchar(10), GETDATE(), 101))
Since DataAppReceived is a datetime, then technically, none of these statements are correct. There is a time element that is not being considered. The CONVERT does remove the time, but you have not addressed the time on the left side of the equation. Therefore, DateAppReceived will probably never equal a date only.
The Between clause is also flawed. GetDate() - 1 returns the current date minus 1 day. But the time is still whatever time it is when GetDate is run! For example:
If I run Select GetDate() - 1 right now, I get
2015-12-28 10:12:32.436
If there are any rows where DataAppReceived is 2015-12-28 and the time component is less than that current time, you will not return those rows.
Using the query posted by Scott is a great way to remove the time component!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 29, 2015 at 10:17 am
Is it because of the performance issue I shouldn't be using it in the where clause:
WHERE CONVERT(varchar(10), a.DateAppReceived, 101) = CONVERT(varchar(10), GETDATE(), 101)
this actually did the job for me.
December 29, 2015 at 10:25 am
I inherited a bad code, I am getting a deadlock when I am using your where clause
December 29, 2015 at 10:25 am
SQLPain (12/29/2015)
Is it because of the performance issue I shouldn't be using it in the where clause:
WHERE CONVERT(varchar(10), a.DateAppReceived, 101) = CONVERT(varchar(10), GETDATE(), 101)this actually did the job for me.
Your query is non-Sargable. If you have an index on DateAppReceived, it will be ignored, table scans will ensue and performance will suffer.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 29, 2015 at 10:37 am
SQLPain (12/29/2015)
I inherited a bad code, I am getting a deadlock when I am using your where clause
Very odd. Any conversion of the column should be vastly more likely to cause deadlocking that just comparing a fixed value to the uncoverted column.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2015 at 10:45 am
It is very odd. thanks for your help guyz
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply