SQL Date Functions

The SQL Date Functions helps us to work with date and time value. For example, to get the Current date or extracting year or month from the column, etc., you need these SQL server functions. In this article, we will show you the available list of SQL Server Date functions along with its description.

SQL Date Functions

In general, the SQL Date function will store in dd-mm-yyyy hh:mm:ss format or any other system-specific format. There are some cases where we have to extract individual parts such as Month, Month Name, Year, Day, etc. In other times, we may have to format the SQL Date, add or subtract a few days from the original. In all these scenarios, we can use the built-in standard SQL Server functions to manipulate the Date.

The following list of tables will show you the available list of Functions. Use these links to learn Sql Server data functions, which includes getting data, formatting datetime etc.

Higher-Precision SQL Date and Time Functions

The following are the high precision functions in SQL Server.

FunctionSyntaxReturn Data TypeDescription
SysDateTimeSYSDATETIME()datetime2(7)It returns the date and time of the system on which the current Server instance is running.
SysDateTimeOffsetSYSDATETIMEOFFSET()datetimeoffset(7)The date and time along with the time zone offset of the system on which the current SQL Server instance is running.
SysUTCDateTimeSYSUTCDATETIME()datetime2(7)This will return the date and time of the computer on which the Server instance is running. Here, the format returns as Coordinated Universal Time or UTC

Lower-Precision SQL Date and Time Functions

The list of low precision Sql Server data and time functions.

FunctionSyntaxReturn Data TypeDescription
Current_TimeStampCURRENT_TIMESTAMPdatetimeIt returns the datetime of the system on which the current Server instance is running.
GetDateGETDATE()datetimeReturns the datetime of the computer on which the Server instance is running.
GetUTCDateGETUTCDATE ()datetimeReturn datetime of the system on which Server instance is running. Here, it returns the format as Coordinated Universal Time or UTC

Gets Date and Time Parts

The following SQL functions help you to extract the date Parts and Time Parts from the user-specified.

FunctionSyntaxReturn Data TypeDescription
DateNameDATENAME (datepart, dt)NVARCHARReturns the specified datename from user specified.
DatePartDATEPART (datepart, dt)intReturns the specified datepart from the user given.
DAYDAY(dt)intReturns the Day part.
MonthMONTH(dt)intThe Month part of the user specified.
YEARYEAR(dt)intIt returns the year part of the user-specified.

Gets Date and Time Values from their Parts

The below SQL functions will return date and Time from the user-specified parts, Or you can say, constructing date and time.

Function NameSyntaxReturn DatatypeDescription
DateFromPartsDATEFROMPARTS (year, month, day)dateIt returns Date value from given parts.
DateTime2FromPartsdatetime2fromparts (year, month, day, hours, minute, seconds, fractions, precision)datetime2 (precision)Returns datetime2 value with specified precision from given parts.
DateTimeFromPartsdatetimefromparts (year, month, day, hour, minute, seconds, milliseconds)datetimedatetime value from given parts.
DateTimeOffsetFromPartsdatetimeoffsetfroomparts (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)datetime (precision)datetimeoffset value with specified offsets and precision from given parts.
SmallDateTimeFromPartssmalldatetimefromparts (year, month, day, hours, minute)smalldatetimeIt returns smalldatetime value from the given parts.
TimeFromPartsTIMEFROMPARTS (hours, minute, seconds, fractions, precision)time (precision)It returns Time value with specified precision.

SQL Functions to Get Date and Time Difference

The Following SQL date functions will help you to find the differences between two dates.

NameSyntaxData TypeDescription
DateDiffDATEDIFF (datepart, startdate, enddate)intIt will calculate the difference between start and end date.
DateDiff_BigDATEDIFF_BIG (datepart, startdate, enddate)bigintSame as DATEDIFF but it return bigint datatype.

SQL Functions to modify Date and Time Values

The below SQL date functions help you to alter or Modify the user-specified date.

Function NameSyntaxReturn Data TypeDescription
DateAddDATEADD(datepart, number, dt)Data type of date argumentThis date function returns a new datetime value by adding user specified number.
EoMonthEOMONTH(start_date, Months_to_add)Date, or return type of the first argument.Returns the last day of the month with an optional offset.
SwitchOffsetSWITCHOFFSET (DATETIMEOFFSET, Time_Zone)datetimeoffset with a fractional precision of the first argument.It changes the time zone offset of a DATETIMEOFFSET value. Remember, it preserves the UTC value
ToDateTimeOffsetTODATETIMEOFFSET (Expression, Time_Zone)datetimeoffset with fractional precision of the first argument.It transforms the datetime2 value into datetimeofset value.

SQL Date Functions that get or set session format

The following shown SQL Server date and time functions will change the default formats. By this, you can change the default return format of a date and time.

NameSyntaxReturn Data TypeDescription
@@DateFirst@@DATEFIRSTtinyintThe current value of the session.
Set DateFirstSET DATEFIRST specific_numberNot ApplicableYou can set the first day of a week from number 1 to 7
Set DateFormatSET DATEFORMAT specific_formatNot ApplicableYou can set the format of a date.
@@Language@@LANGUAGENot ApplicableThis Sql Date function returns the Language that currently in use.
Set LanguageSET LANGUAGE language_NameNot ApplicableYou can Set or change the default language as per your requirement.
sp_helplanguagesp_helplanguage language_NameNot ApplicableIt returns all the information about the given language, and its formats.

Validate Date and Time Values

We can use the below-shown SQL Date functions to validate the given value.

NameSyntaxReturn Data TypeDescription
ISDATEISDATE(Expression)intIt determines whether the input expression is a valid or not.
SQL Date Functions