Posted by Ross Presser on 05/21/05 01:23

On 20 May 2005 10:06:37 -0700, Matt wrote:

> The DATE function takes as input a non-DATE value such as CHAR,
> DATETIME, or INTEGER and returns the corresponding DATE value.

The equivalent of this would be CONVERT:

convert(datetime,'2005-05-14')

But you'd still need dateadd/datediff to do the date arithmetic.

One of the interesting, if convoluted, uses for convert with dates is to
group by an unusual time period. For instance, this would produce a summary
by 6-hour shifts:

SELECT ShiftStart, count(*)
FROM (
SELECT convert(datetime,
floor(convert(float, EntryTime) * 4) / 4) as ShiftStart
FROM Entries
WHERE EntryTime IS NOT NULL
) AS RoundedEntries
GROUP BY ShiftStart
ORDER BY ShiftStart

I tried using 3 instead of 4, but ran into rounding errors: instead of
16:00:00 I was getting 15:59:59.997.

[Back to original message]