Extracting The Date From a SQL DateTime In SQL Queries

less than 1 minute read

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.

Categories:

Updated:

Leave a Comment