DATEPART: The 'real' datepart

2005-10-10 16:42  i292    [permalink]

Let's say you have a table where events were registered, with a datetime field, filled with the current time of the event. Now, how to get a new query that returns the totals per day...

In other languages, it's generally easy to extract only the 'date part' or the 'time part' from a DateTime type of variable. Some languages store the date+time as a floating point variable, the integer part denotes a number of days since a certain day in the past (e.g. 1900-01-01), and the fraction part denotes how far in the day the time part is.

But SQL (at least Microsoft SQL Server) doesn't really have that. There's a DatePart function, but that returns the day of the month, the month of the year...

After a bit of searching around this is apparently the most performant way to get only the date parts (actually, re-work the datetime values to have 'time part' '00:00:00.000'):

CAST(DATEDIFF([DAY], 0, datefield1) AS datetime)

→