Extracting The Date From a SQL DateTime In SQL Queries
September 30 2008 No Comments
Ever wondered how to quickly and easily get the date portion of a datetime field in a SQL query? Seems like it should be easy, right? Unfortunately, it isn’t. I’ve found a lot of solutions, but this one is the most elegant I’ve seen.
CONVERT(datetime, FLOOR(CONVERT(float, DateTime)))
I’ve put this into a user-defined function below:
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[ExtractDate]( @DateTime datetime)RETURNS datetimeASBEGIN RETURN CONVERT(datetime, FLOOR(CONVERT(float, @DateTime)))END
This can be called like so:
SELECT dbo.ExtractDate(DateTime) FROM Table
Credit for this conversion method goes to Matt Berseth, i stumbled across it in the example code for something else.
