Getdate() error

Question

Getdate() error

SQLPain

Hall of Fame

Points: 3522
More actions
December 29, 2015 at 9:04 am

#307406

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))

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

What is the data type of a.DateAppReceived?

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

DATETIME

SELECT STATEMENT

, CONVERT(varchar(10), a.DateAppReceived, 101) AS DateAppReceived

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

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".

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

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/

SQLPain Hall of Fame Points: 3522 More actions · Answer 5

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.

SQLPain Hall of Fame Points: 3522 More actions · Answer 6

I inherited a bad code, I am getting a deadlock when I am using your where clause

LinksUp SSCertifiable Points: 6526 More actions · Answer 7

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/

ScottPletcher SSC Guru Points: 101103 More actions · Answer 8

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".

SQLPain Hall of Fame Points: 3522 More actions · Answer 9

SQLPain

Hall of Fame

Points: 3522

December 29, 2015 at 10:45 am

#1848566

It is very odd. thanks for your help guyz