Where-statement for defining a time horizon

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

Where-statement for defining a time horizon

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-02 : 14:01:53

Hello, I would like to apply a WHERE statement to a column with a timestamp looking like this (called xtime):

"2008-12-08 08:31:09.600"

It should reduce my output to everything which had a timestamp with the time between 08:40:00 to 08:49:59.

How would you write that statement?

Thanks and best

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-02 : 14:09:06

[code]where
dateadd(day, -datediff(day, 0, datecolumn), datecolumn)
between '1900-01-01 08:40:00' and '1900-01-01 08:49:59'[/code]

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-02 : 14:34:27

Thanks for the answer. As I have several dates over a timehorizon of a few months, is it possible to only define the time-interval without mentioning the date?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-04-02 : 15:01:27

Hello;

Yes you can compare just the time only but you need to parse that out.

Here is an example of how to get the time only.

select Convert(varchar,getdate(),8)

output: 12:00:49

r&r

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-02 : 15:16:35

This sounds good but I have no writing permission in that table! so how would you write that statement all together, I know it is possible to combine the statements somehow, how would that work, here my code:

SELECT name, code
FROM timetable
WHERE time1 < xtime < time2

Thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-02 : 15:17:24

quote:

Originally posted by sakets_2000

where 
	dateadd(day, -datediff(day, 0, datecolumn), datecolumn) 
	between '1900-01-01 08:40:00' and  '1900-01-01 08:49:59'

Did you try sakets_2000 solution? It works on any date and should be more efficient than the parsing method.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-02 : 15:20:46

SELECT name, code
FROM timetable
where
dateadd(day, -datediff(day, 0, [time1]), [time1])
between '1900-01-01 08:40:00' and '1900-01-01 08:49:59'

Be One with the Optimizer
TG

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-02 : 15:22:51

yes, but I would have to do it for each date individually, i would like to sort out this time-interval for several months at once..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-02 : 15:33:17

Aren't you talking about 1 datetime column with values that span many different dates and various times? This statement gets ALL rows where the time component is between 8:40 and 8:49 (no matter what the date is). If that is not what you need then post some sample data and expected results, if that is what you need to TRY the statement out.

Be One with the Optimizer
TG

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-04-02 : 17:42:49

quote:
Originally posted by rocco2008

This sounds good but I have no writing permission in that table! so how would you write that statement all together, I know it is possible to combine the statements somehow, how would that work, here my code:

SELECT name, code
FROM timetable
WHERE time1 < xtime < time2

Thanks again

This is how you would write the query above by parsing. It presumes that time1, xtime, and time2 are datetime datatypes.

SELECT name, code
FROM timetable
WHERE Convert(varchar,time1,8) < Convert(varchar,xtime,8) < Convert(varchar,time2,8)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-02 : 18:59:18

Here is a demo of how to do this.


select
	crdate,
	[Time of Day] =
		convert(varchar(20),crdate-dateadd(dd,datediff(dd,0,crdate),0),114)
from
	sysobjects
where
	
	(-- Select on Date Range
	crdate >= '2007-07-23'	and
	crdate < '2009-03-11'
	)
	and
	(-- Select on Time of Day Range
	crdate-dateadd(dd,datediff(dd,0,crdate),0) >= '09:00:00.000' and
	crdate-dateadd(dd,datediff(dd,0,crdate),0) <  '19:00:00.000'
	)


Results:

crdate                                                 Time of Day          
------------------------------------------------------ -------------------- 
2007-07-23 16:27:09.540                                16:27:09:540
2007-07-23 16:27:09.553                                16:27:09:553
2007-07-23 16:28:15.710                                16:28:15:710
2007-07-23 16:28:15.913                                16:28:15:913
2007-12-31 14:39:55.937                                14:39:55:937

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources