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.
| Author |
Topic |
|
BartMan
Starting Member
22 Posts |
 Posted - 2004-05-19 : 12:10:13
|
| Greetings,I have a table with a date time field, and I am trying to be able to request records from a specified date range as well as a time range.After searching the forum I was able to get the date range to work, but I am having difficulty with applying a time range at the same time. For Example: Between the dates of 11/01/2002 and 11/01/2003 I wantthe records that were between 8:30 am to 11:30:00 am.Here is my current query:DateCheck - datetime Field type--------------------------------SELECT * FROM TestTableWHERE ((DateCheck >= '11/01/2002' AND DateCheck <= '11/01/2004' ) AND (CONVERT(varchar(8), DateCheck, 108) >= '8' AND CONVERT(varchar(8), DateCheck, 108) <= '11'))ORDER BY DateCheck--------------------------------This query doesn't handle the 8:30 or 11:30, and sometimes even doesn't return any records at all even though records are in the suggested range. I am not able to get the time range portion to work correctly. Any suggestions?Thanks in advance for any help! |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-05-19 : 12:45:41
|
| See if this or something like it can work:AND DATEDIFF(mi, (CONVERT(datetime, FLOOR(CONVERT(REAL, DateCheck)))), DateCheck) BETWEEN 510 AND 690 |
 |
|
|
BartMan
Starting Member
22 Posts |
Posted - 2004-05-19 : 15:17:18
|
quote:
Originally posted by drymchaser
See if this or something like it can work:AND DATEDIFF(mi, (CONVERT(datetime, FLOOR(CONVERT(REAL, DateCheck)))), DateCheck) BETWEEN 510 AND 690
Thanks for the reply! I think I follow what you are saying to do but I am not surewhere the numbers 510 and 690 are coming from? |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-05-19 : 15:27:56
|
| Calculated from midnight; 8.5 hours * 60 = 510 minutes, 11.5 hours * 60 = 690 minutes |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2004-05-19 : 17:02:17
|
quote:
Originally posted by drymchaser
Calculated from midnight; 8.5 hours * 60 = 510 minutes, 11.5 hours * 60 = 690 minutes
Thanks for the help, that works great!  |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2004-05-20 : 11:30:27
|
| Another item of interests in case someone else is trying to do this, make sure to add the time range to your date, or you won't get the records for the day. The line:(DateCheck >= '11/01/2002' AND DateCheck <= '11/01/2004' )Should become:(DateCheck >= '11/01/2002' AND DateCheck <= '11/01/2004 23:59:59' )Otherwise sql server thinks '11/01/2004' is '11/01/2004 00:00:00'My original query I wasn't doing this, so some of my records were getting cut off from the last day. |
|
|
|
|
|
|
|
|
date and time range query problem?