May 11, 2006 at 7:30 am
Hi,
I have a table that includes two DateTime columns. The first column holds the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the table between a date time range.
i.e return all rows between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Macca
May 11, 2006 at 9:20 am
Prolly something like:
SELECT * FROM MyTable WHERE (MyDate > '2006/5/8' OR (MyDate = '2006/5/8' AND MyTime >= '09:00:00')) AND (MyDate < '2006/5/10' OR (MyDate = '2006/5/10' AND MyTime <= '21:30:00'))
I'm sure there is probably SQL to join the 2 columns together as one date/time, maybe cast one as a Date and cast the other as Time and add them together as a third column in the query or something along those lines but I'd have to dig around and figure it out.
May 12, 2006 at 2:52 am
This is an alternative solution to Sean's, I did a little digging around (look in BOL under the subject CONVERT):
select * from MyTable
where convert( datetime, convert( varchar, MyDate, 106 ) + ' ' + convert( varchar, MyTime, 14 ), 113 ) between convert( datetime, '2006-05-08 09:00:00', 120 ) and convert( datetime, '2006-05-10 21:30:00', 120 )
a little explanation:
- convert the MyDate column to a varchar in format "dd mon yyyy" (106 sets this format)
- convert the MyTime column to a varchar in format "hh:mi:ss:mmm(24h)" (14 sets this format)
- concatinate the two varchars with a space in between which obviously results in a datestring in the format "dd mon yyyy hh:mi:ss:mmm(24h)"
- convert this varchar to a datetime (113 tells Sql server how to interpret the varchar)
- compare to date literals; I used explicit conversion because I like defensive programming, but I suppose that varchar literals like '2006-05-08 09:00:00' will be implicitly converted correctly as well.
If possible, however, I would replace the two columns by one that holds the complete datetime. Note that the MyTime column in fact holds datetimes, the date is probably 1/1/1753 (lower bound of datetime type). Also, the date column may have an additional timepart, which could lead to unexpected and undesired behavior when choosing a solution like Sean proposed (his solution is alright, but only works correctly if the timepart of the datecolumn is always 00:00:00.000).
May 12, 2006 at 3:10 am
I can see your problem. There are three parts to your data.
1) That between date1+1day and date2 (date1 is the date part not including time, note date1 < date2)
2&3) The range of data between date1+Time1 and Date1+1day
and on the other side of the range, the data between date2 and date2 + time2.
So your where clause will inlcude the following (assuming you are looking for the date between say 03 May 2006 11:30 and 12 May 2006 14:00)
('04 May 2006' <=DateField<'12 May 2006') OR
((date = '03 May 2006') AND (TimeField>11:30)) OR
((date = '12 May 2006') AND (TimeField<=14:00))
-- The the use of strict and not strict ranges is controlled by changing (TimeField>11:00) currently inclusive (yes it is '>' i know)
and (TimeField<=14:00) currently inclusive.
Hope this helps.
A.
May 12, 2006 at 5:22 am
Try this..
Your problem is that you want to compare your two separate 'date' and 'time' fields with a combined DateTime value. It would be nice to just add the 'date' and 'time' together like this:
MyDateTime = MyDateColumn + MyTimeColumn
But unfortunately this DOES NOT WORK, because the MyTimeColumn also contains a datepart value (albiet normally a hidden date value). So what we have to do is identify this value, which we can do like this:
(convert(datetime, floor(convert(float, MyTimeColumn))))
Now we can extract this from our proper 'date' value before adding the 'time' so the hidden date element is effectively cancelled out, like this:
MyDateTime = (MyDateColumn - (convert(datetime, floor(convert(float, MyTimeColumn))))) + MyTimeColumn
So our SQL would look like this:
SELECT * FROM MyTable
WHERE (MyDateColumn - (convert(datetime, floor(convert(float, MyTimeColumn))))) + MyTimeColumn
BETWEEN '2006/5/8 09:00:00' AND '2006/5/10 21:30:00'
Good Luck!!
May 12, 2006 at 9:17 am
Macca,
My first suggestion would be what Kevin offered: MyDateTime = MyDateColumn + MyTimeColumn. Then realized he was correct in that this will not work if your time value contains a date as well. In working with datetime values I always try treating them a simple floating point values, which is what they truely are. Integer portion represents date, fraction portion represents time. Suggest trying :
MyDate + MyTime - FLOOR(CONVERT(FLOAT,MyTime)) BETWEEN .....
Same result, but a little easier to read..
Mike
May 14, 2006 at 2:40 pm
Going down the route of adding the date and time together for comparision is fine, HOWEVER SQL server will not make effective use of indexes that are defined for the two columns in question. (That is of course if any indexes are defined.)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply