Date Query

how would i query the table with dateid for a particular date
DateID
2005-11-24 14:40:00
select * from tbl1 where dateid like '2005-11-24%' ?
how about date and time like 9-11am of somethingUmmm.. How about

select * from tbl1 where dateid >= '2005-11-24 09:00:00' and dateid < '2005-11-24 12:00:00'|||For the date only, you can:
SELECT * FROM tbl1 WHERE CONVERT(varchar(10), DateID, 101) = '2005-11-24'

For the date/time range, you would just use BETWEEN, as in:
SELECT * FROM tbl1 WHERE DateID BETWEEN '2005-11-24 09:00:00' AND '2005-11-24 11:00:00' (or use bmalar's suggestion above).|||I'd like to see just how that BETWEEN works. I'm thinking at at the very least you'll need to decorate the DateID a bit.

-PatP|||I dunno 'bout all that... ;)

I didn't have anything to get too fancy with testing, but thisSELECT * FROM currentportfolio
WHERE createdate
BETWEEN '2005-11-24 23:59:59' AND '2005-11-25 00:00:01'
managed to find all the rows in my DB with a smalldatetime that was '2005-11-25 00:00:00' (actually inserted by doing the ole' faithful CONVERT(varchar(10), GETDATE(), 101)

and this:
SELECT *
FROM eventlog
WHERE date BETWEEN '2005-11-25 13:55:02' AND '2005-11-25 13:55:03'
order by date

managed to find everything in my table with a datetime within the two seconds indicated (9 rows)

whatchew talkin' 'bout, Willis? What means "decorate"?|||I must have been doing drugs...

The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression... I wanted to see just how that would work!

-PatP|||do u mean just specify date without time or time without date? i usually use datediff and datepart

Nov 24, 2005
select * from tbl1 where datediff(day,dateid,'20051124')=0

09:00 - 11.00
select * from tbl1 where (datepart(hour,dateid) between 9 and 10) or (datepart(hour,dateid)=11 and datepart(minute,dateid)=0)|||The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression... That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.|||That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.

ROTFLMAO :D

Like, um, okay dude...|||That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.
What scares me about that is I read it and it sounded like perfect English to me ;) But I blame my two high-school aged daughters for that...the older boys, thank goodness, graduated without ever uttering the word "like" in a sentence in that way...