Essential SQL Server Date, Time and DateTime Functions
March 22, 2008 — vincenthomeStandard Date, Time & TimeSpan Functions
function DateOnly(@DateTime DateTime)
– Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetimecreate 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 datetimecreate 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 datetimecreate function TimeOnly(@DateTime DateTime)
– returns only the time portion of a DateTime, at the “base” date (1/1/1900)
returns datetimecreate 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 datetimecreate 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 datetimecreate 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 intFor 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)
