comparing times - 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)

comparing times

Author

Topic

makimark
Starting Member

34 Posts

Posted - 2003-09-11 : 07:17:20

Hi

I have a time range for off-peak, peak rates. 20:00 to 07:00. When i try to compare the time using datepart(hour, myfulldate) it returns the result as an integer not time. Hence the following is always false given the time of 20:33:12
if myfulldate >= 20 and < 7 then do soemething. the myfulldate is datetime in yyyy-mm-dd HH:mm:ss.

any ideas ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-11 : 07:33:11

Change it to:

IF DatePart(hour, myfulldate)>=20 OR DatePart(hour, myfulldate)<7 THEN ....

Or you could use the logical converse:

IF NOT (DatePart(hour, myfulldate) BETWEEN 7 AND 19) THEN ....

Whichever one seems most logical to you.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-11 : 08:27:35

Of course, if you have an index on myfulldate you can construct your predicate like ...

where myfulldate > dateadd(hh,20,dateadd(dd,datediff(dd,0,myfulldate),0)) or
myfulldate < dateadd(hh,7 ,dateadd(dd,datediff(dd,0,myfulldate),0))

(Note: the ">" or ">=" operators may need to be adjusted based on your business rules)

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 08:42:51

As Rob points out, the main issue you are having has nothing to do with dates or hours, because:

WHERE X >= 20 and X < 7

(with x being any single value)

will NEVER be true because X cannot be both >= 20 AND less than 7 at the same time .... thus, you need to re-structure your clause using one of the options Rob presented.

- Jeff

tacket
Starting Member

47 Posts

Posted - 2003-09-11 : 15:32:30

First convert your datetime column to a varchar, then convert that to a datetime. Something like this:

declare @storedGetdate smalldatetime

select @storedgetdate = '9/11/2003 7:00 PM'

if
(
convert(smalldatetime,convert(varchar(10),@storedgetDate,108)) between convert(smalldatetime,'8:00 PM') and convert(smalldatetime,'12:00 AM')
OR
convert(smalldatetime,convert(varchar(10),@storedgetDate,108)) between convert(smalldatetime,'12:00 AM') and convert(smalldatetime,'7:00 AM')
)
Begin
select 'true'
end
else
Begin
select 'false'
end

Obviously, replace @storedGetdate with whatever variable you are using... This way you are DEFINATELY compairing apples to apples.

tacket
Starting Member

47 Posts

Posted - 2003-09-11 : 15:43:23

I forgot one tiny detail :).. btw I like Robvolk's implementation better than mine.. but anyway. here is another way:

declare @storedGetdate smalldatetime

select @storedgetdate = '9/11/2003 12:01 AM'

if
(
convert(smalldatetime,convert(varchar(10),@storedgetDate,108)) between convert(smalldatetime,'8:00 PM') and convert(smalldatetime,'12:00 AM') + 1
OR
convert(smalldatetime,convert(varchar(10),@storedgetDate,108)) between convert(smalldatetime,'12:00 AM') and convert(smalldatetime,'7:00 AM')
)
Begin
select 'true'
end
else
Begin
select 'false'
end

The + 1 will add a day so in essence you are compairing 1/1/1900 8:00 PM and '1/2/1900 12:00 AM'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources