query on a date using 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 2000 Forums

Transact-SQL (2000)

query on a date using LIKE

Author

Topic

drman
Starting Member

38 Posts

Posted - 2003-07-23 : 13:47:19

I have created a stored proc to create a recordset containing all records that match on a particular date. If the user enters '07/23/2003' as the date the widgets were created, I can't find a match using

select * from table where createdate = '07/23/2003' because the create date field is a smalldatetime that may be 2003-07-23 08:00:00.

I tried using LIKE and that did not give me the desired results (nothing matched)

I know this is basic for you guys, but what am I doing wrong??

Thanks in advance.

drman

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 13:58:19

SELECT * FROM TABLE WHERE CONVERT(VARCHAR(50), createdate, 101) = '07/23/2003'

See CAST and CONVERT in BOL. I used style 101 because that is the format that you are using. Have a look at the other styles though.

Tara

drman
Starting Member

38 Posts

Posted - 2003-07-23 : 14:12:10

thanks for the quick reply. Let me try that. I was working with cast also, but i don't think I had the correct syntax

drman

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 14:17:12

quote:

thanks for the quick reply. Let me try that. I was working with cast also, but i don't think I had the correct syntax

drman

CAST doesn't allow the style option, so CONVERT is used instead. You could also use DATEPART to get the same results but the CONVERT solution is easier.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 14:39:55

Can you say SCAN

Say it with me...SCAN

Jeff, where are you?


USE Northwind
GO

DECLARE @x varchar(8), @y varchar(8), @myDate varchar(10), @myStartDate datetime, @myEndDate datetime

SELECT @x = '00:00:00', @y = '23:59:59', @myDate = '1998-04-08'

SELECT @myStartDate = Convert(datetime,@myDate + ' '+ @x), @myEndDate = Convert(datetime,@myDate + ' ' + @y)

SELECT * FROM Orders WHERE OrderDate BETWEEN @myStartDate AND @myEndDate

Brett

8-)

Edited by - x002548 on 07/23/2003 14:41:51

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 14:42:46

And Brett is correct, you should use the BETWEEN option instead. Although both options works, BETWEEN is better due to the SCAN required on my solution.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 15:36:34

quote:

And Brett is correct, you should use the BETWEEN option instead. Although both options works, BETWEEN is better due to the SCAN required on my solution.

Tara

And that's only because Jeff beat on me for suggesting the same thing the other day...how soon we forget....

I was just mooing around....

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-23 : 15:40:08

Brett, agree about the scan, but I'm not too keen on what you did instead.
If you use BETWEEN, you'll almost always find yourself going through some effort to lose the end of the range (in particular, you chopped off 299 ticks too many). It seems to me a lot simpler and safer to add a whole day for the end date and use:
OrderDate >= @myStartDate AND OrderDate < @myEndDate

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 15:56:58

quote:

Brett, agree about the scan, but I'm not too keen on what you did instead.
If you use BETWEEN, you'll almost always find yourself going through some effort to lose the end of the range (in particular, you chopped off 299 ticks too many). It seems to me a lot simpler and safer to add a whole day for the end date and use:
OrderDate >= @myStartDate AND OrderDate < @myEndDate

Looks good

Never argue with the Arnold

But what do you mean by 299? I'm sure you mean microseconds, but isn't it

11:59:59:999?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 16:20:42

Ah ... Brett, you beat me to it !

good job ....

I agree with Arnold (of course!). avoid the BETWEEN operator with dates UNLESS you are guaranteed none of them have a time component.

where date >= @date and date < dateadd(dd,1,@date)

is usually how i handle these cases ....

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-23 : 16:24:50

The precision of datetime really is 1/300 second: the 4 bytes that represent the time part are actually stored as the number of 1/300 second intervals -- ticks -- since midnight.
I think I probably picked up the term in this context from Kalen Delaney e.g. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9147
But it's pretty common in general programming parlance as the smallest storable interval.
So when you specify a millisecond value, it gets rounded to a number of ticks... and consequently SELECT CONVERT(datetime, '1900-01-01 23:59:59:999') returns 1900-01-02 00:00:00.000.

Edited by - Arnold Fribble on 07/23/2003 16:28:29

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 16:29:44

[quote]
Never argue with the Arnold
[/quote

Damn, I sure knew that was comming....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources