Where clause difficulty with dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where clause difficulty with dates

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-18 : 06:01:08

Hi All,

I have a where clause in my query as follows:

WHERE ([Cases].reference IS NOT NULL)
AND

(CONVERT(varchar, [Cases].date, 103) = GETDATE() - 1)
OR
(CONVERT(varchar, [More Cases].[Datev2], 103) = GETDATE() - 1)
OR
(CONVERT(varchar, [Other Cases].[Datev3], 103) = GETDATE() - 1)

When I run this query, I get an error saying 'The conversion of a char data type to a date time data type resulted in an out of range date time value'.

Basically I want this clause to restrict results to only show records (rows) where there is a reference number present, and any of three possible columns contains a date that equals yesterday. Can anyone spot what I am doing wrong?

Thanks in advance
Humate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 06:07:39

Try like this:-

WHERE ([Cases].reference IS NOT NULL)
AND

((DATEADD(d,DATEDIFF(d,0, [Cases].date),0) = DATEADD(d,DATEDIFF(d,0, DATEADD(d,-1,GETDATE())),0)
OR
DATEADD(d,DATEDIFF(d,0, [More Cases].[Datev2]),0) = DATEADD(d,DATEDIFF(d,0, DATEADD(d,-1,GETDATE())),0)
OR
DATEADD(d,DATEDIFF(d,0, [Other Cases].[Datev3]),0) = DATEADD(d,DATEDIFF(d,0, DATEADD(d,-1,GETDATE())),0))

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-18 : 06:36:49

Excellent, seems to do the trick for my query with an extra bracket thrown in

I'll test again tomorrow, many thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 06:48:22

And if speed is a concern, use this

WHERE	Cases.Reference IS NOT NULL
	AND Cases.Date >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
	AND Cases.Date < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')
	AND [More Cases].Datev2 >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
	AND [More Cases].Datev2 < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')
	AND [Other Cases].Datev3 >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
	AND [Other Cases].Datev3 < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')

because then you can utilize any present index over the datetime columns.

E 12°55'05.25"
N 56°04'39.16"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-18 : 07:17:16

hmm, that looks good. The query runs far quicker, but I don't get any results this time. Something to do with date formats maybe?

The 3 columns have dates in the format dd/mm/yyyy (converted in the select part of the query eg: SELECT CONVERT(varchar, [Cases].date, 103)) - does that have any affect on the code I should enter?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-18 : 12:51:42

Yes, it does have an affect, don't covert your DATETIME to VARCHAR. Looks at what Peso is doing and, if you do not understand the value of what he is showing you, please ask and we can help you. The basic idea behind Peso's code is that you do not need to apply any functions to your DATETIME and therefore the optimizer can use an index (assume one exists) and provide faster queries.

Here is an article about SQL Server DATETIMEs that might help:[url]http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 13:23:11

The three date columns are DATETIME or SMALLDATETIME?
Or are they VARCHAR?

E 12°55'05.25"
N 56°04'39.16"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-19 : 09:50:48

I thought this must be the problem. I tried altering the format of Peso's example without success, so assumed it must be because my data is converted into a Varchar data type (dd/mm/yyyy).

When i try the code again without converting to varchar, it works just fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:24:06

Great!
Have a nice weekend.

By the way, if I suggest a differnt approach as above, why don't why copy and paste first and then try, before rewriting the code?

E 12°55'05.25"
N 56°04'39.16"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-19 : 13:46:07

Two more long days before a nice weekend i'm afraid

I did try your code first, then tried changing it, neither worked. I then removed the convert from my select statement as suggested by Lamprey, which worked with your original code. Much quicker now, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources