What is the easiest way to extract only the date from a datetime field?
I want to be able to group by a date in a datetime field and ignore the time part.

I've tried to use left(datetime, 11) but it reformats the date as MMMM dd ccyy (the original format was mm/dd/ccyy). To be honest, the format that I would prefer is either ccyy/mm/dd or ccyy.mm.dd.

Do I need to write a function to get this date format?

Rand

__________________
Rand

I created a function to convert my AS400 DB2 dates,

CREATE FUNCTION [ufn_AS400Date] (@InDate datetime)
RETURNS varchar(10) AS
BEGIN
    RETURN LEFT(CONVERT(VarChar(20), @InDate, 120), 10)
END

Check out the Convert function in BOL.


David

Thanks,

It does exactly what is needed!

:D

Rand

You can use this instead. without having to extract the 10 characters from the left, after conversion.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection