Remove the time from a DATETIME

On 11 May, 2015

T-SQL

By : Dave Jenkins

No Comments

I’ve seen alot of questions around the internet about how to remove the time portion from a date. I’ve provided two ways of doing this below that you can use.
The first uses a CONVERT method, however, CONVERT is Microsoft SQL Server function meaning that it will not port to another database server such as MySQL. So the second example is one that uses CAST, which will port to other database servers as its an ANSI standard.
Personally, I use the CONVERT method in my day job as it’s the standard that we use, I suggest that you follow your workplace standards too, if there aren’t any, you can lead the way and make them!

DECLARE @Today DATETIME;
SET @Today = GETDATE();

-- Using Convert
SELECT
	CONVERT(FLOAT, @Today) as ConvertToFloat,
	FLOOR(CONVERT(FLOAT, @Today)) as ConvertToFloatAndFloored,
    CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, @TODAY))) as BackToDateTime

-- Using Cast
SELECT
	CAST(@Today as FLOAT) as CastToFloat,
	FLOOR(CAST(@Today as FLOAT)) as CastToFloatAndFloored,
	CAST(FLOOR(CAST(@Today as FLOAT)) AS DATETIME) as BackToDatetime

-- Use.
SELECT
	MyData
FROM
	dbo.MyTable
WHERE
	CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, MyDate))) >= '01 JAN 2015'

DECLARE @Today DATETIME;

SET @Today = GETDATE();

-- Using Convert

SELECT

CONVERT(FLOAT, @Today) as ConvertToFloat,

FLOOR(CONVERT(FLOAT, @Today)) as ConvertToFloatAndFloored,

CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, @TODAY))) as BackToDateTime

-- Using Cast

SELECT

CAST(@Today as FLOAT) as CastToFloat,

FLOOR(CAST(@Today as FLOAT)) as CastToFloatAndFloored,

CAST(FLOOR(CAST(@Today as FLOAT)) AS DATETIME) as BackToDatetime

-- Use.

SELECT

MyData

FROM

dbo.MyTable

WHERE

CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, MyDate))) >= '01 JAN 2015'

About The Author

Dave Jenkins

Hello! I'm Dave Jenkins and I have been working with MS SQL Server for two years. I'm an MCP (70-461) working towards an MCSA. I love working with MS SQL Server and the BI Stack.

Number of Posts : 26

All Posts by : Dave Jenkins