Extract time range. - 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

General SQL Server Forums

New to SQL Server Programming

Extract time range.

Author

Topic

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-04-19 : 12:19:26

I have a table with a column formatted as datetime. I need to extract records that occured between a certain time on certain days. For example, my query will find the dates i need with:

"tout <= '3/31/2009 09:30:00 am' and tout >= '3/1/2009 1:00:00 am'"

But i need to break this down farther to between '1:00:00 am' and '9:30:00 am' on those days. Is there a simple way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 14:42:09

cast(tout as datetime) <= cast('3/31/2009 09:30:00 am' as datetime) and cast(tout as datetime) >= cast('3/1/2009 1:00:00 am' AS datetime)

E 12°55'05.63"
N 56°04'39.26"

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-19 : 16:10:58

Hard to tell, but he may be saying he wants to apply the time filter (1 am --> 930 am) for each day?





declare @d table (tout datetime)
insert into @d
	select '2009-03-30 9:29:00.000' union
	select '2009-03-15 9:31:00.000' union
	select '2009-02-01 9:29:00.000' union
	select '2009-04-01 9:31:00.000'


select * from @d
where cast(tout as datetime) <= cast('3/31/2009 09:30:00 am' as datetime) and cast(tout as datetime) >= cast('3/1/2009 1:00:00 am' AS datetime)

-- vs

select  tout, 
	tout_time,
	case 
		when tout_time between '1900-01-01 01:00:00.000' and '1900-01-01 09:30:00.000'
		then 1 
		else 0 
	end [inTimeRange]
	
from	(	select	tout [tout], 
			tout - dateadd(dd,0, datediff(dd,0, tout)) [tout_time]
		from	@d
		where	tout between cast('3/1/2009 1:00:00 am' as datetime) and cast('3/31/2009 09:30:00 am' as datetime)
	) d

Nathan Skerl

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-04-20 : 13:47:14

Nathan,
That was what I was going after, and thanks. This seemed to work correcty!

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-20 : 14:16:56

Ok, that query was just to help us clarify. Glad to see youre on the right track. Post back if you run into trouble.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources