1. Comparing datetime field (containing date, time values) to a date only string
Hi all
I'm wondering if anybody can help me with comparing a datetime field (containing date, time values in the form mm/dd/yy 0:00:00.000) to a datetime variable containing only a date value.
My SQL statement below ideally should return the following 1 row:
count catid category state
1 602 Performance British Columbia
startdate enddate
2001-11-06 18:00:00.000 2001-11-07 00:00:00.000
however, it currently returns none (without erroring).
select count(*) as count, events.catid, category, state, otherstate, startdate, enddate from events join
event_categories on events.catid=event_categories.catid where events.catid between 600 and 699 and
(state='British Columbia' or otherstate='British Columbia') and convert(datetime, startdate, 101)
<=convert(datetime, '11/6/2001' , 101) and convert(datetime, enddate, 101)>=convert(datetime, '11/6/2001' , 101)
group by events.catid, category, state, otherstate, startdate, enddate
When I modify the above SQL statement (shown below) by adding '10pm' after the '11/6/2001' where I compare the startdate value to 11/6/2001, I do receive the row.
select count(*) as count, events.catid, category, state, otherstate, startdate, enddate from events join
event_categories on events.catid=event_categories.catid where events.catid between 600 and 699 and
(state='British Columbia' or otherstate='British Columbia') and convert(datetime, startdate, 101)
<=convert(datetime, '11/6/2001 10pm' , 101) and convert(datetime, enddate, 101)>=convert(datetime, '11/6/2001' , 101)
group by events.catid, category, state, otherstate, startdate, enddate
I'm also confused as to why the convert functions in the above SQL statement doesn't truncate the time portion of the startdate value, changing the current value "2001-11-06 18:00:00.000" to "11/6/2001". I tried using convert(char, startdate, 101) and that does truncate off the time from the date, however, the >= and <= comparison operators fail.
Any feedback would be much appreciated!
Darren
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
2. Connecting SQLserver 6.5 to Netscape Enterprise server
3. Concatenating datetime field and int field to make a date time
4. Neede: Resellers for Adv. Comp. Products.(only serious replies).
5. Treating datetime fields as separate date and time fields
6. pl sql question
7. ADO with Access Date/Time field not storing the time, just the date
8. Using Application Functions in SQL Server
9. how to change dates or times in a oracle date time field
10. Storing the date and time in a datetime field
11. Current date and time as the Default Value for a DateTime Field
12. java.sql.Date producing a time element when passed to SQL Server datetime field
13. how do I separately retrieve the time and date from a datetime field
by