Searching between datetime value

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)

Searching between datetime value

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2008-04-18 : 17:07:54

I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as smalldatetime but am having an awful time.
This works:
SELECT aDate
FROM aTbl
WHERE aDate> '4/16/2008 07:59'

This returns no records, but I can see there are records:
SELECT aDate
FROM aTbl
WHERE (aDate> '4/16/2008 07:59' and aDate<= '4/16/2008 11:59')

This also doesn't return records:
SELECT aDate
FROM aTbl
WHERE aDate between '4/16/2008 07:59' and '4/16/2008 20:00'

Nor does this:
SELECT aDate
FROM aTbl
WHERE (aDate> '4/16/2008 07:59' and aDate< '4/17/2008')

I have searched google and looked books online and cannot find any examples of how to do this.

If anyone can help me out, I would appreciate it.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 17:15:35

What does

SELECT aDate
FROM aTbl

return?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 17:16:04

Can you show us the records that should be returned?

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

ann
Posting Yak Master

220 Posts

Posted - 2008-04-18 : 17:17:07

the select adate from tbl returns lots of records - I am only using the terminology of adate and tbl as an example.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 17:17:41

Post the table definition/layout.

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

ann
Posting Yak Master

220 Posts

Posted - 2008-04-18 : 17:22:48

this is what it should return (sample only):
2008-04-16 08:15:00
2008-04-16 08:15:00
2008-04-16 08:15:00
2008-04-16 11:05:00
2008-04-16 11:03:00
2008-04-16 11:02:00
2008-04-16 11:06:00
2008-04-16 08:15:00
2008-04-16 10:57:00

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 17:26:18

That looks good. You must be using varchar data type for this column. I realize your post says smalldatetime though.

Post the CREATE TABLE statement for the table.

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

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-19 : 06:28:48

ACTUALLY WHAT IS HAPPENING WHEN YOU ARE COMPARING SAME DATE THEN THE CONDITION ALWAYS FALSE LIKE
'07/16/2008 08:47:00'>'07/16/2008 07:46'
HERE IT IS FALSE BECAUSE ONE THAT CAN NOT BE GREATER THEN THE SAME DATE.
YOU ARE RIGHT BECAUSE TIME IS GREATER BUT HERE TIME IS NOT COMPARING.
YOU NEED SOME WAY TO COMPARE TIME ALSO.

chandan Joshi

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-19 : 07:08:03

SELECT * FROM @T WHERE
CONVERT(VARCHAR(10),CONVERT(datetime,ADATE,101),112)
BETWEEN convert(VARCHAR(10),convert(datetime,'07/16/2008 09:47:00',101),112)
AND convert(varchar(10),convert(datetime,'07/16/2008 10:47:00',101),112)

YOU CAN DO LIKE THIS WAY ALSO
HERE ADATE IS YOUR TABLE COLUMN WHICH CONTAIN DATE

chandan Joshi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-19 : 08:22:07

Ann,
There is nothing wrong with the WHERE options that you posted (above).
I suspect there is something else going on outside the context of the sql queries. Are you running these directly in a query analyzer window or through an application?

All these statement work fine:


declare @aTbl table (aDate smallDateTime)

insert @aTbl (aDate)
select '2008-04-16 08:15:00' union all
select '2008-04-16 08:15:00' union all
select '2008-04-16 08:15:00' union all
select '2008-04-16 11:05:00' union all
select '2008-04-16 11:03:00' union all
select '2008-04-16 11:02:00' union all
select '2008-04-16 11:06:00' union all
select '2008-04-16 08:15:00' union all
select '2008-04-16 10:57:00' union all
select '2008-04-17 10:57:00'

--This returns no records, but I can see there are records:
SELECT aDate
FROM @aTbl
WHERE (aDate> '4/16/2008 07:59' and aDate<= '4/16/2008 11:59')

--This also doesn't return records:
SELECT aDate
FROM @aTbl
WHERE aDate between '4/16/2008 07:59' and '4/16/2008 20:00'

--Nor does this:
SELECT aDate
FROM @aTbl
WHERE (aDate> '4/16/2008 07:59' and aDate< '4/17/2008')

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-24 : 08:33:25

Also, please post your table definition, with all columns and their data types. My money is still on that you are using a VARCHAR datatype for that column ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources