Formatting a date in an SQL query

[AccessUser22]

My dates are stored as smalldatetime in my database. Dates are entered along with the time an item is expected to start on, i.e. '9/15/2006 8:00:00 AM'

If I want to strip away the time so that I can do a group by and get a count of how many entries I have on each day, how do I do this? I tried looking for an answer in books online, I found the DATEONLY function but it looks like this doesn't work in the query analyzer. Is there a way to say Format([scheduledate],'mm/dd/yyyy') ? I tried that and it won't work either.

FYI I'm using SQL Server 2005. Any info is appreciated. Thanks.

 

[gmmastros]

You can use convert, but I suggest this instead.

Select DateAdd(Day, DateDiff(Day, 0, [!]GetDate()[/!]), 0)

Replace the part in red with your actual field name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AccessUser22]

THANKS! Worked perfectly. Just what I needed.