Transcript T-SQL Date and Time Functionsx

T-SQL Date and
Time Functions
Said Salomon
Said Salomon
Unitrin Direct Insurance
[email protected]

I have over 25 year experience IT. I have a vast array
of abilities in the field in the areas of Network,
Desktop Support, DBA, Staff Project Management,
Application Software Development, Business
Analysis and Quality Assurance. I have Microsoft
certifications as MCTS, MCPS, and MCNPS, and
multiple certifications from the Insurance Institute
of America. Currently I am a DBA at Unitrin Direct
Insurance.

DateTime
 DECLARE @MyDatetime datetime
 January 1, 1753, through December 31, 9999
 00:00:00 through 23:59:59.997
 Accuracy Rounded to increments of .000, .003, or
.007 seconds
 Storage size 8 bytes

DECLARE @MySmalldatetime
smalldatetime
 January 1, 1900, through June 6, 2079
 00:00:00 through 23:59:59
 Accuracy One minute
 Size 4 bytes, fixed.

DECLARE @MyDatetime2 datetime2(7)
 January 1,1 AD through December 31, 9999 AD
 00:00:00 through 23:59:59.9999999
 Accuracy 100 nanoseconds
 Storage size 6 bytes for precisions less than 3; 7
bytes for precisions 4 and 5. All other precisions
require 8 bytes.

DECLARE @MyDatetimeoffset
datetimeoffset(7)
 January 1,1 AD through December 31, 9999 AD
 00:00:00 through 23:59:59.9999999
 Time zone offset range -14:00 through +14:00
 Accuracy 100 nanoseconds
 Storage size 10 bytes, fixed is the default with the
default of 100ns fractional second precision.

DECLARE @MyDate date
 January 1, 1 A.D. through December 31, 9999 A.D.
 No time part
 Accuracy One day
 Storage size 3 bytes, fixed

DECLARE @MyTime time(7)
 No Date part
 00:00:00 through 23:59:59.9999999
 Accuracy 100 nanoseconds
 Storage size 5 bytes, fixed, is the default with the
default of 100ns fractional second precision.


GetDate()
Returns the current database system
timestamp as a datetime value without the
database time zone offset. This value is
derived from the operating system of the
computer on which the instance of SQL
Server is running.


DATEADD (datepart , number, date )
Returns a specified date with the specified
number interval (signed integer) added to a
specified datepart of that date.


DATEDIFF ( datepart , startdate , enddate )
Returns the number of date and time
boundaries crossed between two specified
dates.


DATENAME ( datepart ,date )
Returns a character string that represents
the specified datepart of the specified date


DATEPART ( datepart , date )
Returns an integer that represents the
specified datepart of the specified date.

Returns an integer representing the
day datepart of the specified date.


GETUTCDATE()
Returns the datetime value that represents
the current UTC time (Coordinated Universal
Time or Greenwich Mean Time). The current
UTC time is derived from the current local
time and the time zone setting in the
operating system of the computer on which
the instance of Microsoft SQL Server is
running.


MONTH ( date )
Returns an integer that represents the month
part of a specified date.


YEAR ( date )
Returns an integer that represents the year
part of a specified date.


SYSDATETIME ()
Returns a datetime2(7) value that contains
the date and time of the computer on which
the instance of SQL Server is running.


SYSDATETIMEOFFSET ()
Returns a datetimeoffset(7) value that
contains the date and time of the computer
on which the instance of SQL Server is
running. The time zone offset is included.


SYSUTCDATETIME ( )
Returns a datetime2 value that contains the
date and time of the computer on which the
instance of SQL Server is running. The date
and time is returned as
UTC time (Coordinated Universal Time). The
fractional second precision specification has a
range from 1 to 7 digits. The default precision
is 7 digits.



CURRENT_TIMESTAMP
Returns the current database system
timestamp as a datetime value without the
database time zone offset. This value is
derived from the operating system of the
computer on which the instance of SQL
Server is running.
This function is the ANSI SQL equivalent
to GETDATE.


SWITCHOFFSET
(DATETIMEOFFSET,time_zone)
Returns a datetimeoffset value that is
changed from the stored time zone offset to
a specified new time zone offset.


TODATETIMEOFFSET
(expression,time_zone)
Returns a datetimeoffset value that is
translated from a datetime2 expression.


ISDATE (expression )
Returns 1 if the expression is a
valid date, time, or datetime value;
otherwise, 0.

Not really date time functions but they can be
useful to format or change date and times



Date Time Functions Functions (books
Online)
http://bit.ly/cpcrlN
SQL Server 2008, Dev Edition
http://bit.ly/L2hJQ
My Twitter
SaidSalomon