query with dates - 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 2008 Forums

Transact-SQL (2008)

query with dates

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-05-06 : 02:09:21

I have a table that has
starttime and endtime as varchar fields

so it could be for exmaple

08:00 18:00

or

18:00 end time 08:00

how can I query by passing in a time and checking if it's between the start and the end

and if the time is 18:00 - 08:00 then 07:00 should be counted as ok but 09:00 should not.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-06 : 05:58:15

[code]declare @Sample table(txt varchar(255),Starttime varchar(10),Endtime varchar(10))
insert @Sample
select 'A','06:00','18:00' union all
select 'B','09:00','15:00' union all
select 'C','18:00','07:00'

declare @Searchtime time
set @Searchtime = '19:45'

select * from @Sample
where
(
datediff(second,Starttime,Endtime) >= 0 and @Searchtime between Starttime and Endtime
)
or
(
datediff(second,Starttime,Endtime) <= 0 and (@Searchtime >= Starttime or @Searchtime <= Endtime)
)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources