Problem with Select Query - 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)

Problem with Select Query

Author

Topic

kallileo
Starting Member

21 Posts

Posted - 2007-10-16 : 15:55:40

Can anybody explain to me why I get nothing when I execute:

SELECT *FROM data
WHERE (timestamp >= 18 / 08 / 2007) AND (timestamp <= 21 / 08 / 2007)

timesamp
19/8/2007 10:25:59 am
21/8/2007 10:25:27 am
21/8/2007 10:46:11 am
21/8/2007 10:46:21 pm
23/8/2007 11:56:22 pm
23/8/2007 12:22:46 am
23/8/2007 12:23:17 am
24/8/2007 12:32:19 pm

data is the table and timestamp the datetime column

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-16 : 16:13:57

[code]

Select *
Into #TmpDates
From
(
select convert(datetime,'19/8/2007 10:25:59 am',103) as [TimeStamp]
Union All
select convert(datetime,'21/8/2007 10:25:27 am',103)
Union All
select convert(datetime,'21/8/2007 10:46:11 am',103)
Union All
select convert(datetime,'21/8/2007 10:46:21 pm',103)
Union All
select convert(datetime,'23/8/2007 11:56:22 pm',103)
Union All
select convert(datetime,'23/8/2007 12:22:46 am',103)
Union All
select convert(datetime,'23/8/2007 12:23:17 am',103)
Union All
select convert(datetime,'24/8/2007 12:32:19 pm',103)
) a

Select * from #tmpDates
where [TimeStamp] between Convert(Datetime,'18/08/2007',103) and Convert(Datetime,'22/08/2007',103)
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-16 : 16:17:28

You need to encapsulate your dates in single-quotes (and you should use YYYYMMDD format):

SELECT *
FROM data
WHERE (timestamp >= '2007-08-18') AND (timestamp <= '2007-08-21')

As an example, try this and see what the results are:

SELECT 18 / 08 / 2007, CAST(18 / 08 / 2007 AS DATETIME)

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:18:56

You need to put quotes around your string dates, and you should use 'YYYYMMDD' format so that they are server-locale-independent and un-ambiguous.

A date of
18 / 08 / 2007
(with no quotes) is a numeric division! (which will give an answer of Zero

Krsiten

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 02:32:08

quote:
Originally posted by Kristen

You need to put quotes around your string dates, and you should use 'YYYYMMDD' format so that they are server-locale-independent and un-ambiguous.

A date of
18 / 08 / 2007
(with no quotes) is a numeric division! (which will give an answer of Zero

Krsiten

Thats one disadvantages of implitcit conversions
I expect it to return error if dates are not sorrounded by single quotes

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 02:36:02

"I expect it to return error if dates are not sorrounded by single quotes"

Well I'm with you on that one ... although I can see myself doing:

SET @MyDate = (2007-1900) * 365 + (2007-1900) / 4 + 10 * 30 + 10 / 2 + 17)

for an approximate date

(actually it gives 19-November ...)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 03:13:06

quote:
Originally posted by Kristen

"I expect it to return error if dates are not sorrounded by single quotes"

Well I'm with you on that one ... although I can see myself doing:

SET @MyDate = (2007-1900) * 365 + (2007-1900) / 4 + 10 * 30 + 10 / 2 + 17)

for an approximate date (actually it gives ~~19-November...~~Error)

Kristen

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:47:52

Yeah, well I did a

select convert(datetime, (2007-1900) * 365 + (2007-1900) / 4 + 10 * 30 + 10 / 2 + 17)

to test it and assume with would work with a SET.

That "assume" thing never works

Kristen

kallileo
Starting Member

21 Posts

Posted - 2007-10-17 : 11:50:21

Thank you guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources