Sunny Books
What we have

SQL Server Date and Time Functions 1

The floowings are SQL Server builtin functions for getting system date and time values

Function Return Type Format Description
GETDATE() datetime 2012-02-07 10:10:18.453 commonly used
CURRENT_TIMESTAMP datetime 2012-02-07 10:10:18.453 ANSI SQL equivalent to GETDATE
SYSDATETIME () datetime2(7) 2012-02-07 10:11:32.5669319 more fractional seconds precision
SYSDATETIMEOFFSET() datetimeoffset(7) 2012-02-07 10:12:29.2347029 -05:00 more fractional seconds precision + time zone offset
GETUTCDATE() datetime 2012-02-07 15:12:58.120 UTC data and time
SYSUTCDATETIME() datetime2(7) 2012-02-07 15:13:48.1880754 UTC data ans time, with more fractional seconds precision

The floowings are SQL Server builtin functions that get date and time parts

ISDATE(): checks if the given value is a valid date, time, or datetime. It returns 1 for success, 0 for failure.

DAY(): returns the "day number of the month" of the given date

MONTH(): returns the "month number of the year" of the given date

YEAR(): returns the "year number" of the given date

DATENAME ( datepart , date ): returns a string (nvarchar) that represents a part of the given date. This function takes 2 peramenters: the first parameter datepart specifies the part of the date that we want, the second parameter is the actual date from which we want the part of the Date.

DATEPART ( datepart, date ): returns an interger (int) representing the specified datepart. This function is similar to DATENAME(). DATENAME() returns nvarchar while DATEPART() returns an integer.

datepart abbreviation
year yy,yyyy
quarter qq,q
month mm,m
dayofyear dy,y
day dd,d
week wk,ww
weekday dw
hour hh
minute mi,n
second ss,s
millisecond ms
microsecond mcs
nanosecond ns
Tzoffset tz

Example

SELECT DAY(GETDATE()) 				returns 9
SELECT MONTH(GETDATE()) 			returns 2
SELECT YEAR(GETDATE()) 				returns 2013

SELECT DATENAME(YEAR, '2012-11-12') 		returns 2012
SELECT DATENAME(MONTH, '2012-11-12') 		returns November
SELECT DATENAME(WEEKDAY, '2012-11-12') 		returns Monday
SELECT DATENAME(MINUTE, '2012-11-12 20:35:47') 	returns 35

SELECT DATEPART(YEAR, '2012-11-12') returns 2012
SELECT DATEPART(MONTH, '2012-11-12') returns 11
SELECT DATEPART(MINUTE, '2012-11-12 20:35:47') returns 35

Another Example

SELECT name, birthday,  DAY(birthday) as day_number,
   DATENAME(WEEKDAY, birthday) AS week_day, 
   MONTH(birthday) as month_number,
   DATENAME(MONTH, birthday) as month_name,
   YEAR(birthday) as year	
FROM [em].[employee]

and the result

name birthday day_number week_day month_number month_name year
David 1978-09-10 16:34:12.000 10 Sunday 9 September 1978
Alice 1980-12-20 09:25:07.000 20 Saturday 12 December 1980
Rose 1979-09-24 12:36:43.000 24 Monday 9 September 1979
Jack 1984-04-16 23:32:07.000 16 Monday 4 April 1984
Tom 1982-08-15 16:58:04.000 15 Sunday 8 August 1982
Alexa 1986-11-09 09:10:28.000 9 Sunday 11 November 1986
Chantal 1980-01-23 06:25:43.000 23 Wednesday 1 January 1982
Ella 1982-02-20 12:30:26.000 20 Saturday 2 February 1982
Kaven 1978-05-25 16:08:27.000 25 Thursday 5 May 1978
Erik 1982-06-11 10:32:50.000 11 Friday 6 June 1982

SUNWEB EXPERT