Built-in Date functions in SQL Server

Hi All,

Hope you all are doing good.

In this article, we will know about various T-SQL Date functions and their uses.

Function	Date Time Format	Description
GETDATE()	2020-01-27 16:10:08.367	Commonly used function
CURRENT_TIMESTAMP	2020-01-27 16:19:54.630	ANSI SQL equivalent to GETDATE
SYSDATETIME()	2020-01-27 16:20:03.0860709	More fractional seconds precision
SYSDATETIMEOFFSET()	2020-01-27 16:20:10.3577345 +05:30	More fractional seconds precision + Time zone offset
GETUTCDATE()	2020-01-27 10:50:17.793	UTC Date and Time
SYSUTCDATETIME()	2020-01-27 10:50:24.8673609	UTC Date and Time, with More fractional seconds precision

ISDATE() - Checks whether the given value is a valid date, time, or datetime. 
Returns 1 for success, 0 for failure.

Examples:
Select ISDATE('SUNEEL') -- returns 0
Select ISDATE(Getdate()) -- returns 1
Select ISDATE('2020-01-27 21:02:04.167') -- returns 1

Note: For datetime2 values, IsDate returns ZERO.

Example:
Select ISDATE('2020-01-27 11:34:21.1918447') -- returns 0

Day() - Returns the 'Day number of the Month' of the given date

Examples:
Select DAY(GETDATE()) -- Returns the day number of the month, based on current system datetime.
Select DAY('01/27/2020') -- Returns 27

Month() - Returns the 'Month number of the year' of the given date

Examples:
Select Month(GETDATE()) -- Returns the Month number of the year, based on the current system date and time
Select Month('01/27/2020') -- Returns 1

Year() - Returns the 'Year number' of the given date

Examples:
Select Year(GETDATE()) -- Returns the year number, based on the current system date
Select Year('01/27/2020') -- Returns 2020

DateName(DatePart, Date) - Returns a string, that represents a part of the given date. This functions takes 2 parameters. The first parameter 'DatePart' specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the Date.

Valid Datepart parameter values

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

Examples:
Select DATENAME(Day, '2020-01-27 16:10:08.367') -- Returns 27
Select DATENAME(WEEKDAY, '2020-01-27 16:10:08.367') -- Returns Monday
Select DATENAME(MONTH, '2020-01-27 16:10:08.367') -- Returns January

DatePart(DatePart, Date) - Returns an integer representing the specified DatePart. This function is simialar to DateName(). DateName() returns nvarchar, where as DatePart() returns an integer. The valid DatePart parameter values are in above table. 

Examples:
Select DATEPART(weekday, '2020-01-27 16:10:08.367') -- returns 2
Select DATENAME(weekday, '2020-01-27 16:10:08.367') -- returns Monday

DATEADD (datepart, NumberToAdd, date) - Returns the DateTime, after adding specified NumberToAdd, to the datepart specified of the given date.

Examples:
Select DateAdd(DAY, 20, '2020-01-27 16:10:08.367') -- returns 2020-02-16 16:10:08.367
Select DateAdd(DAY, -20, '2020-01-27 16:10:08.367') -- returns 2020-01-07 16:10:08.367

DATEDIFF(datepart, startdate, enddate) - Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

Examples:
Select DATEDIFF(MONTH, '01/27/2020','03/31/2020') -- returns 2
Select DATEDIFF(MONTH, '03/31/2020','01/27/2020') -- returns -2
Select DATEDIFF(DAY, '01/27/2020','03/31/2020') -- returns 64
Select DATEDIFF(DAY, '03/31/2020','01/27/2020') -- returns -64

Thanks for reading this article!!