Stripping the time component from a datetime value

Here is a very simple and elegant way to strip the time component from a datetime value:

CAST(FLOOR(CAST(@date AS float)) AS datetime)

Written as a function:

CREATE FUNCTION BareDate
(
– Add the parameters for the function here
@date datetime
)
RETURNS datetime
AS
BEGIN
RETURN CAST(FLOOR(CAST(@date AS float)) AS datetime)
END
GO

When we pass in the value ‘2008-02-12 13:25:33.3′, it returns the value 2008-02-12 00:00:00.000

Other ways of stripping the time component:

SELECT DATEADD(dd,(DATEDIFF(dd,0,backup_start_date)),0)

or:

SELECT CAST(DATEDIFF(dd,0,backup_start_date) AS Datetime)Using the convert has performance issues.  I’ll have to come up with numbers for all these: or

SELECT CONVERT(Datetime, CONVERT(NCHAR(10), backup_start_date, 121))

This entry was posted on 7. April 2008 at 09:58 and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print