Query datetime-field by date when field contains date + time

Query datetime-field by date when field contains date + time

Post by Jask » Thu, 13 Dec 2001 19:33:52



I have tablefield (sqlserver2000) which datatype is datetime. I have
inserted value "insert into ... datetimefield = {ts '2001-11-24
08:24:12'}..." in it. How can I find (query) that row by date ?

I have tried :
... where datetimefield like {d '2001-11-24'}...
... where datetimefield = {d '2001-11-24'}...
... where datetimefield like {d '2001-11-24%'}...
... where datetimefield like {d '%2001-11-24%'}...

..but query never returns that row. Whats wrong ?!?!

This works :
... where datetimefield = {ts '2001-11-24 08:24:12'}...
but time is unknown in "real life".

Connectionstring  is like:
"Provider=SQLOLEDB;Data Source=SERVERNAME;Initial
Catalog=tempdbase;User Id=sa;Password="

And same problem in  access database ?
... where datetimefield = #11/24/2001#...
... where datetimefield like #11/24/2001#...

Maybe its better to use string-datatype instead datetime...

Jaska

 
 
 

Query datetime-field by date when field contains date + time

Post by Sue Hoegemeie » Fri, 14 Dec 2001 23:21:34


You need to convert the datetime field to just the date
portion so try something like:
where convert(varchar(10), YourDateTimeField, 101) =
'11/24/2001'

--Sue



Quote:>I have tablefield (sqlserver2000) which datatype is datetime. I have
>inserted value "insert into ... datetimefield = {ts '2001-11-24
>08:24:12'}..." in it. How can I find (query) that row by date ?

>I have tried :
>... where datetimefield like {d '2001-11-24'}...
>... where datetimefield = {d '2001-11-24'}...
>... where datetimefield like {d '2001-11-24%'}...
>... where datetimefield like {d '%2001-11-24%'}...

>..but query never returns that row. Whats wrong ?!?!

>This works :
>... where datetimefield = {ts '2001-11-24 08:24:12'}...
>but time is unknown in "real life".

>Connectionstring  is like:
>"Provider=SQLOLEDB;Data Source=SERVERNAME;Initial
>Catalog=tempdbase;User Id=sa;Password="

>And same problem in  access database ?
>... where datetimefield = #11/24/2001#...
>... where datetimefield like #11/24/2001#...

>Maybe its better to use string-datatype instead datetime...

>Jaska


 
 
 

Query datetime-field by date when field contains date + time

Post by Umachandar Jayachandra » Mon, 17 Dec 2001 02:02:53


    This will use the index on column efficiently & is better even
otherwise. The trick is to ignore the time part by specifying the correct
day boundaries.

where datetimefield >= CONVERT( varchar, {ts '2001-11-24 08:24:12'}, 112 )
and
      datetimefield <  CONVERT( varchar, {ts '2001-11-24 08:24:12'} + 1,
112 )
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

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