Datetime question - 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

SQL Server Development (2000)

Datetime question

Author

Topic

ultraman
Starting Member

18 Posts

Posted - 2004-11-01 : 12:01:37

Hi !

I have a DB with BeginDate and EndDate fields. Both are DateTime. I need to retreive the rows that are between specific dates, but only in a particular time range too.

An exemple will help makes things clearer I think ;-)

0037549-01015 | 7266 | 2004-11-01 10:33:00 | 2004-11-01 11:10:00
0142350-01030 | 7161 | 2004-10-29 14:09:20 | 2004-10-29 15:16:10
0142350-01030 | 7031 | 2004-10-29 14:09:27 | 2004-10-29 15:16:1
0142350-01030 | 7085 | 2004-10-29 14:09:36 | 2004-10-29 15:16:10
0142350-01030 | 7161 | 2004-10-29 15:21:29 | 2004-10-29 16:06:29
0142350-01030 | 7250 | 2004-10-29 15:21:30 | 2004-10-29 16:06:29

Retreive rows where BeginDate (3rd col) are between '2004-10-28' and '2004-11-01' BUT ONLY those between '7:00 AM' and '3:15 PM'...

In this sample, this should return me rows 2, 3 and 4 only.

Is there an easy way to make it ?

--------------
Ultraman

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-01 : 12:09:36

IMO that requires 2 conditions.
...(DATEADD(Day, DATEDIFF(Day, 0, beginDate), 0) between @date1 and @date2)
AND Convert(varchar(10), beginDate, 108) between @time1 and @time2

Go with the flow & have fun! Else fight the flow

ultraman
Starting Member

18 Posts

Posted - 2004-11-01 : 13:50:24

One friend gave me this solution using DATEPART wich works fine :

SELECT * FROM TABLE WHERE
BeginDate between '2004-10-28' and '2004-11-01' 
and (DATEPART(hh, BeginDate) between 7 and 14 or DATEPART(hh, BeginDate) = 15 and DATEPART(mi, BeginDate) <=15)

--------------
Ultraman

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-01 : 13:53:06

yeah that's even better because its probably faster

Go with the flow & have fun! Else fight the flow

ultraman
Starting Member

18 Posts

Posted - 2004-11-01 : 14:12:01

Cool then, I'll use this solution. Thanks a lot spirit1 !

--------------
Ultraman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources