April 30, 2014

SQL Server Date & Time - Data Types and its Function

For more details: http://msdn.microsoft.com/en-us/library/ms186724.aspx

Date & Time Data Types:

For more detail about data types, you can see in sys.systypes


You can find this on SQL Server 2008, 2008 R2 and 2012. Its default literal format is YYYY-MM-DD



For example:

Declare @date date1;
Declare @date date2;

set date1 = '20-09-2012'
set date2 = '20-09-2012 12:40:30'

Select date1
Output: 2012-09-20
Select date2
Output: 2012-09-20


You can find this on SQL Server 2008, 2008 R2 and 2012. This will return time of the day without timezone and is based on 24 hours.


time[(fractional second precision)]

For Example:

Declare @time4 time(4)
Declare @time7 time(7)
Declare @time time

set time4 = '12:45:25.1234'
set time7 = '12:45:25.1234567'
set time = '2012-09-10 12:45:25.123' 

select time4
Output: 12:45:25.1234
select time7
Output: 12:45:25.1234567
select time
Output: 12:45:25.1230000


You can find this on SQL Server 2000, 2005, 2008, 2008 R2, 2012. This returns date with time of a day (2 hour format) with seconds always 00 and without fractional seconds. Seconds are rounded to minutes depending upon the value of seconds. The value which is less than 29.998 is rounded to the nearest minutes and the value which is greater than 29.999 is rounded to its nearest minutes.  This will not return fractional seconds.

small datetime

For Example:

Declare @smalldatetime1 smalldatetime = '2012-09-13 12:45:31' -- will round to next minute
Declare @smalldatetime2 smalldatetime = '2012-09-13 12:45:29' -- will not round to next minute

Select @smalldatetime1
Output: 2012-09-13 12:46:00
Select @smalldatetime2
Output: 2012-09-13 12:45:00


You can find this on  SQL Server 2000, 2005, 2008, 2008 R2 and 2012. This returns date with time of a day and fractional seconds based on 24 hour.


For Example:

Declare @datetime1 datetime = '12-23-35'
Declare @time1 time(4) = '11:10:05.1234'
Declare @datetime2 datetime = @time1

Select @datetime1
Output: 2035-12-23 00:00:00.000
Select @datetime2
Output: 1900-01-01 11:10:05.1234


It can be considered as a extension of datetime but this holds large date range along with default precision. You can find this only in SQL Server 2008, 2008 R2 and 2012.

datetime2 [(fractional seconds precision)]

For Example:
Declare @datetime2_1 datetime2(4) = '12-13-25 12:32:10.1234'
Declare @datetime2_2 datetime2 = '12-13-25 11:32:10.1234567'
Declare @datetime2_3 datetime2 = '12-13-25'

Select @datetime2_1
Output: 2025-12-13 12:32:10.1234
Select @datetime2_2
Output: 2025-12-13 11:32:10.1234567
Select @datetime2_3
Output: 2025-12-13 00:00:00.0000000


You can find this on SQl Server 2008, 2008 R2, and 2012. This holds date along with time of a day and contains timezone awareness based on 24 hour clock.

datetimeoffset[(fractional seconds precision)]

For Example:
Declase @datetimeoffset datetimeoffset) = '12-13-25 12:32:10 +05:45'
Declare @time time(3) = @datteimeoffset
Declare @date date = @datetimeoffset

Select @datetimeoffset 
Output: 2025-12-13 12:32:10.0000 +05:45 
Select @time
Output: 12:32:10.000
Select @date
Output: 2025-12-13

Date Time Function:


It helps to get database system timestamp with fractional seconds as a datetime without time zone offset.


Select GetDate()


Same as GetDate().


Same as GetDate() but the value represents the current UTC time.





It returns a character string for the specified part of the date.

DateName(datepart, date)

For Example:
Declare @date datetime
set @date = '2012-12-30 12:25:10.013'

Select @date
Output: 2012-12-30

Select datename(year, @date) 
Select datename(yy, @date) 
Select datename(yyyy, @date) 
Output: 2012

Select datename(quarter, @date)
Select datename(qq, @date)
Select datename(q, @date)
Output: 4

Select datename(month, @date)
Select datename(mm, @date)
Select datename(m, @date)
Output: December

Select datename(dayofyear, @date)
Select datename(dy, @date)
Select datename(y, @date)
Output: 364

Select datename(day, @date)
Select datename(dd, @date)
Select datename(d, @date)
Output: 30

Select datename(week, @date)
Select datename(ww, @date)
Select datename(wk, @date)
Output: 53

Select datename(weekday, @date)
Select datename(dw, @date)
Select datename(w, @date)
Output: Monday

Select datename(hour, @date)
Select datename(hh, @date)
Output: 12

Select datename(minute, @date)
Select datename(mi, @date)
Select datename(n, @date)
Output: 25

Select datename(second, @date)
Select datename(ss, @date)
Select datename(s, @date)
Output: 10

Select datename(millisecond, @date)
Select datename(ms, @date)
Output: 13


It is similar to datename but it returns as an integer.

datepart( datepart, date)

For Example:
Declare @date datetime
set @date = '2012-12-30 12:25:10.013'

Select @date
Output: 2012-12-30

Select datepart(year, @date) 
Select datepart(yy, @date) 
Select datepart(yyyy, @date) 
Output: 2012

Select datepart(quarter, @date)
Select datepart(qq, @date)
Select datepart(q, @date)
Output: 4

Select datepart(month, @date)
Select datepart(mm, @date)
Select datepart(m, @date)
Output: 12

Select datepart(dayofyear, @date)
Select datepart(dy, @date)
Select datepart(y, @date)
Output: 364

Select datepart(day, @date)
Select datepart(dd, @date)
Select datepart(d, @date)
Output: 30

Select datepart(week, @date)
Select datepart(ww, @date)
Select datepart(wk, @date)
Output: 53

Select datepart(weekday, @date)
Select datepart(dw, @date)
Select datepart(w, @date)
Output: 2

Select datepart(hour, @date)
Select datepart(hh, @date)
Output: 12

Select datepart(minute, @date)
Select datepart(mi, @date)
Select datepart(n, @date)
Output: 25

Select datepart(second, @date)
Select datepart(ss, @date)
Select datepart(s, @date)
Output: 10

Select datepart(millisecond, @date)
Select datepart(ms, @date)
Output: 13


It is similar to Datepart(Day, date) and if date contains only time then it will return value as 1.

Syntax: Day(date)


It is similar to Datepart(Month, date) and if date contains only time then it will return value as 1.

Syntax: Month(date)


It is similar to Datepart(Year, date) and if date contains only time then it will return value as 1900.

Syntax: Year(date)


It checks whether the given expression is valid datetime/ date/ time. It returns value as 0 or 1. If the value is true, it returns 1 else 0.

Syntax: Isdate(expression)

For Example:
Select IsDate('2012-12-25 12:30:25.1234567') Returns 1
Select IsDate(NULL) Returns 0


It returns a specified date with the specified number interval added to specified datepart of that date. datepart cannot be User-defined variable or its equivalents.

DateAdd(datepart, number, date)

For Example:

Declare @date datetime
Set @date = '2012-12-30 12:30:15.013'

Select dateadd(year, 1, @date)
Select dateadd(yy, 1, @date)
Select dateadd(yyyy, 1, @date)
Output: 2013-12-30 12:30:15.013

Select dateadd(month, 1, @date)
Select dateadd(mm, 1, @date)
Select dateadd(m, 1, @date)
Output: 2013-01-30 12:30:15.013'

Select dateadd(quarter, 1, @date)
Select dateadd(qq, 1, @date)
Select dateadd(q, 1, @date)
Output: 2013-03-30 12:30:15.013'


It returns difference between startdate and enddate based on datepart.

DateDiff( datepart, startdate, enddate)

For Example:

Declare @date1 datetime = '2012-12-30 12:30:10.123'
Declare @date2 datetime = '2013-05-25 10:15:20.123'

Select datediff (year, @date1, @date2)
Output: 1

No comments: