Where MyDateField LIKE - SQL Server Forums

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 MyDateField LIKE

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-03 : 12:26:10

Ok, educate me on this one.

This works:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate LIKE 'Oct 28 2010%'

Now, this does NOT work:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate LIKE 'Nov 02 2010%'

The dates are in the same format.
There are records for both in the table.
Why does the second one not bring back and data?

Thanks,

Zath

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-03 : 12:37:03

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate LIKE 'Nov 2 2010%'

You should use yyyy/mm/dd as the date format to avoid issues like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-03 : 12:54:58

Actually you shouldn't use LIKE matches on datetime columns at all:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate BETWEEN cast('Nov 02 2010' as datetime) AND cast('Nov 02 2010 23:59:59' as datetime)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-03 : 13:03:28

+1 to Rob's post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-03 : 15:13:38

The BETWEEN seems to work a bit better.

Now, the next question for that WHERE statement:

It's for a search of course and there will be other parameters.
Maybe the date will be there or it will be null.
So if it's null, exclude it from the search like I did with this one:

WHERE pk.CreateDate BETWEEN cast('Nov 02 2010' as datetime) AND cast('Nov 02 2010 23:59:59' as datetime)
AND (@Batch IS NULL OR (ISNULL(pk.Batch,'') = @Batch))
AND (@BP IS NULL OR (ISNULL(pk.BusinessPartner,'') = @BP))

I've tried a few variations just can't get it right for the date...

Thanks,

Zath

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 16:01:09

I don't see a parameter for your hard coded date values.

No, you're never too old to Yak'n'Roll if you're too young to die.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-03 : 21:29:08

WHERE pk.CreateDate BETWEEN cast(@dDate as datetime) AND cast(@dDate + ' 23:59:59' as datetime)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 04:04:04

WHERE (@dDate IS NULL OR (pk.CreateDate BETWEEN cast(@dDate as datetime) AND cast(@dDate + ' 23:59:59' as datetime)))
AND (@Batch IS NULL OR (ISNULL(pk.Batch,'') = @Batch))
AND (@BP IS NULL OR (ISNULL(pk.BusinessPartner,'') = @BP))

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-11-04 : 04:39:32

quote:
Originally posted by robvolk

Actually you shouldn't use LIKE matches on datetime columns at all:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate BETWEEN cast('Nov 02 2010' as datetime) AND cast('Nov 02 2010 23:59:59' as datetime)

Variation (which I prefer), in case OP wants to consider it, is:


WHERE     pk.CreateDate >= cast('Nov 02 2010' as datetime) 
      AND pk.CreateDate <  cast('Nov 03 2010' as datetime)

this can be expressed programatically:


WHERE     pk.CreateDate >= cast('Nov 02 2010' as datetime) 
      AND pk.CreateDate <  DATEADD(Day, 1, cast('Nov 02 2010' as datetime))

and if 'Nov 02 2010' is in a @LocalVariable or @Parameter as:


WHERE     pk.CreateDate >= @MyDateVariable
      AND pk.CreateDate <  DATEADD(Day, 1, @MyDateVariable)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-04 : 06:54:49

quote:
Originally posted by robvolk

Actually you shouldn't use LIKE matches on datetime columns at all:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate BETWEEN cast('Nov 02 2010' as datetime) AND cast('Nov 02 2010 23:59:59' as datetime)

There will be missing data if you use 23:59:59. If time part matters, Kristen's method is the correct one

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-04 : 06:57:24

quote:
Originally posted by Zath

Ok, educate me on this one.

This works:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate LIKE 'Oct 28 2010%'

Now, this does NOT work:

SELECT pk.Key_ID
FROM Key pk
WHERE pk.CreateDate LIKE 'Nov 02 2010%'

The dates are in the same format.
There are records for both in the table.
Why does the second one not bring back and data?

Thanks,

Zath

If you haven't used proper DATETIME datatype, try using it. It will fix 99% of the problems you face with VARCHAR dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources