The SQL Server provides various SQL Date Functions, which helps us to work with date and time value. For example, to get the Current date, or extracting year or month from the date column etc you need these sql server date functions.
In this article we will show you the available list of Date functions in SQL Server along with its description.
SQL Date Functions
The following list of tables will show you the available list of SQL Date and Time Functions
Higher-Precision SQL Date and Time Functions
The following are the high precision data and time functions in Sql Server.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| SysDateTime | SYSDATETIME() | datetime2(7) | It returns the date and time of the system on which the current Server instance is running. |
| SysDateTimeOffset | SYSDATETIMEOFFSET() | datetimeoffset(7) | This SQL Date and time function returns the date and time along with time zone offset of the system on which the current SQL Server instance is running. |
| SysUTCDateTime | SYSUTCDATETIME() | datetime2(7) | This will return date and time of the computer on which Server instance is running. Here, the date and time is returned as Coordinated universal Time, or UTC Time |
Lower-Precision SQL Date and Time Functions
The list of low precision Sql Server data and time functions are as shown below.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| Current_TimeStamp | CURRENT_TIMESTAMP | datetime | It returns the date and time of the system on which the current Server instance is running. |
| GetDate | GETDATE() | datetime | Returns the date and time of the computer on which the Server instance is running. |
| GetUTCDate | GETUTCDATE () | datetime | This Date function return date and time of the system on which Server instance is running. Here, date and time is returned as Coordinated universal Time, or UTC Time |
SQL Date Functions to gets date and Time Parts
Below SQL Date functions helps you to extract the date Parts, and Time Parts from the user specified dates.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| DateName | DATENAME(datepart, date) | NVARCHAR | This SQL Date function returns the specified date part from user specified date. |
| DatePart | DATEPART(datepart, date) | int | Returns the specified date part from user given date. |
| DAY | DAY(date) | int | Returns the Day part of the specified date. |
| Month | MONTH(date) | int | This Date function returns the Month part of the user specified date. |
| YEAR | YEAR(date) | int | It returns the year part of the user specified date. |
SQL Date Functions to gets date and Time Values from their Parts
Below functions will return date and Time from the user specified parts, Or you can say, constructing date and time.
| Function Name | Syntax | Return Datatype | Description |
|---|---|---|---|
| DateFromParts | DATEFROMPARTS (year, month, day) | date | It returns Date value from given parts. |
| DateTime2FromParts | DATETIME2FROMPARTS(year, month, day, hours, minute, seconds, fractions, precision) | datetime2(precision) | Returns datetime2 value with specified precision from given parts. |
| DateTimeFromParts | DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds) | datetime | returns datetime value from given parts. |
| DateTimeOffsetFromParts | DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) | datetime(precision) | Returns datetimeoffset value with specified offsets and precision from given parts. |
| SmallDateTimeFromParts | SMALLDATETIMEFROMPARTS (year, month, day, hours, minute) | smalldatetime | It returns smalldatetime value from the given parts. |
| TimeFromParts | TIMEFROMPARTS (hours, minute, seconds, fractions, precision) | time(precision) | It returns Time value with specified precision. |
SQL Date Functions to get Date and Time Difference
The Following functions will help you to find the differences between two dates.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| DateDiff | DATEDIFF(datepart, start_date, end_date) | int | This Date and time function calculate the difference between start date and end date. |
| DateDiff_Big | DATEDIFF_BIG(datepart, start_date, end_date) | bigint | Same as DATEDIFF but it return bigint data type. |
SQL Date and Time Functions to modify date and Time Values
Below functions helps you to alter or Modify the user specified date.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| DateAdd | DATEADD(datepart, number, date) | Data type of date argument | This date function returns a new datetime value by adding user specified number. |
| EoMonth | EOMONTH(start_date, Months_to_add) | Date, or return type of the first argument. | Returns the last day of the month with an optional offset. |
| SwitchOffset | SWITCHOFFSET (DATETIMEOFFSET, Time_Zone) | datetimeoffset with fractional precision of the first argument. | It changes the time zone offset of a DATETIMEOFFSET value. Remember, it preserves the UTC value |
| ToDateTimeOffset | TODATETIMEOFFSET (Expression, Time_Zone) | datetimeoffset with fractional precision of the first argument. | This Date and time function transforms the datetime2 value into datetimeofset value. |
SQL Date Functions that get or set session format
Below 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.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| @@DateFirst | @@DATEFIRST | tinyint | This Date function returns the current value of the session. |
| Set DateFirst | SET DATEFIRST specific_number | Not Applicable | You can set the first day of a week from number 1 to 7 |
| Set DateFormat | SET DATEFORMAT specific_format | Not Applicable | You can set the format of a date. |
| @@Language | @@LANGUAGE | Not Applicable | This Sql Date function returns the Language that is currently being used. |
| Set Language | SET LANGUAGE language_Name | Not Applicable | You can Set or change the default language as per your requirement. |
| sp_helplanguage | sp_helplanguage language_Name | Not Applicable | It returns all the information about the given language, and its date formats. |
SQL Date Functions to validate date and Time Values
You can use below shown Date and time function to validate the given value.
| Function Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| ISDATE | ISDATE(Expression) | int | It determines whether the input expression is a valid date or time value. |
