Essential SQL Server Date, Time and DateTime Functions

Standard Date, Time & TimeSpan Functions

function DateOnly(@DateTime DateTime)
– Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime

create function Date(@Year int, @Month int, @Day int)
– returns a datetime value for the specified year, month and day
– Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime

create function Time(@Hour int, @Minute int, @Second int)
– Returns a datetime value for the specified time at the “base” date (1/1/1900)
– Many thanks to MVJ for providing this formula (see comments).
returns datetime

create function TimeOnly(@DateTime DateTime)
– returns only the time portion of a DateTime, at the “base” date (1/1/1900)
returns datetime

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
– returns a dateTime value for the date and time specified.
returns datetime

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
– returns a datetime the specified # of days/hours/minutes/seconds from the “base” date of 1/1/1900 (a “TimeSpan”)
returns datetime

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
– returns the # of units specified in the TimeSpan.
– The Unit parameter can be: “d” = days, “h” = hours, “m” = minutes, “s” = seconds
returns int

For More Details: Essential SQL Server Date, Time and DateTime Functions

see also:

• SQL# CLR
• Working with Time Spans and Durations in SQL Server
• Group by Month (and other time periods)
• Working with Date and/or Time values in SQL Server: Don’t Format, Don’t Convert — just use DATETIME
• Data Types — The Easiest Part of Database Design
• How to format a Date or DateTime in SQL Server
• Breaking apart the DateTime datatype — Separating Dates from Times in your Tables
• Date Only and Time Only data types in SQL Server 2005 (without the CLR)