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:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s