Extracting The Date From a SQL DateTime In SQL Queries

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ExtractDate]
(
@DateTime datetime
)
RETURNS datetime
AS
BEGIN
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.

Leave a Reply