I have a SQL Server 2014 database with 2 fields:
- one of type date for the record date
- one of type time for the record time.
I want to retrieve records from the table between 2 dates/ times. Example: from 2015-01-01 at 16:00 until 2015-01-02 at 08:00.
I tried
SELECT...
Date BETWEEN '2015-01-01' AND '2015-01-02'
AND Time BETWEEN '16:00' AND '08:00'
This fails, as expected.
Can I do what I want or only if I use a single field for date and time (datetime type)?
Try adding the fields:
WHERE cast(date as datetime)+cast(time as datetime) between
'2015-01-01 16:00' AND '2015-01-02 8:00'
Your query will only work if want to find records within a certain time window between certain days. E.g. during office hours between 13 and 17 July.
WHERE [Date] BETWEEN '2015-07-13' AND '2015-07-17'
AND CONVERT(Time, [Time]) BETWEEN '08:00' AND '16:00'
And since your start Time value is greater than your end time value, you will not get any results until you combine the date and time into one value like:
WHERE CONVERT(DateTime, [Date]) + CONVERT(DateTime, [Time])
BETWEEN '2015-01-01 16:00' AND '2015-01-02 08:00'
You can concatenate and then convert the fields together to form a datetime as such:
select *
from thing
where convert(datetime, convert(varchar(10), myDate) + ' ' + convert(varchar(10), myTIme)) between '2015-01-01 00:00:00' and '2015-01-02 01:01:00'
note I'm assuming your date is in a date column, and your time is in a time column
see fiddle:
http://sqlfiddle.com/#!6/3a308/8
If most of your queries are going to be on a datetime rather than a date or a time, consider adding a datetime column instead, as queries like the above tend to be inefficient due to needing to run a convert on unnecessary rows in order to determine if they should be included. Perhaps a computed column could help with this as well.
datetime column. I have made already some tests and I can move from one colum of date type plus a column of time type to a single column of datetime without loosing data.