SQL Query Starting time

Question

trying to find a query to select data that will start at specific time 08:00 till the next day at 08:00, i have separate date and time fields and also selecting specific criteria from date to date.

select * from table 
where datefield between '2014-01-14' and '2014-01-14' 
      and timefield between '08:00' and '08:00'

You are selecting records from the same day and time. What RDMS are you using? If you're using SQL Server you can use `DATEADD`. — Darren, Jan 14 '14 at 10:03
Thanks for your cooperation, we are using MS SQl Server, DATEADD as i understand is for datetime fields, we need to add criteria for separate time and date fields — user3193462, Jan 14 '14 at 10:16

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

Don't understand why datefield and timefield is not saved in a datetime-column. Also I don't know what timefield is, but if < and > works on the datatype, this will work:

select * from table 
where (datefield = '2014-01-14' and timefield >= '08:00')
or (datefield = '2014-01-15' and timefield < '08:00')

Edit: Just learned you can just add a date and time field together to a datetime

select * from table 
where (datefield + timefield) >= '2014-01-11 08:00'
and (datefield + timefield) < '2014-01-13 08:00'

Here's the source of adding them together

edited May 23 '17 at 10:33

Community

1
1

answered Jan 14 '14 at 10:15

Robert Fricke

3,637
21
34

Great but it doesn't work if we select other dates select * from table where (datefield = '2014-01-11' and timefield >= '08:00') or (datefield = '2014-01-13' and timefield < '08:00') – user3193462 Jan 14 '14 at 10:30
Problem is that you are killing the performance when comparing fields like that (second solution). Which is why I went out of my way to avoid that solution – t-clausen.dk Jan 14 '14 at 11:21
Oh and furthermore you can't add a date and a time. You can however add a datetime and a time. – t-clausen.dk Jan 14 '14 at 11:43
Oh, ok. So might have to `convert(datetime,datefield) + timefield`. I agree with the performance issue. Thats's why I would prefer to save it as `datetime` in one column from the start. – Robert Fricke Jan 14 '14 at 12:01

score 0 · Answer 2 · answered Jan 14 '14 at 10:27

declare @from datetime = '2014-01-14 08:00'
declare @to   datetime = '2014-01-15 08:00'
select * from table
where datefield between cast(@from as date) and @to
and not(datefield = cast(@from as date) and timefield < cast(@from as time))
and not(datefield = cast(@to as date) and timefield > cast(@to as time))

SQL Query Starting time

2 Answers2