The SQL Server Date Functions helps us to work with date and time value. For example, you need these functions to get the Current date or extract year or month from the column, etc. In this article, we will show you the available list of Date functions along with its description.
SQL Date Functions
The SQL Server Date function will generally store in dd-mm-yyyy hh:mm:ss format or any other system-specific format. However, there are some cases where we have to extract individual parts such as Month, Month Name, Year, Day, etc. Sometimes, we may have to format the Date and add or subtract a few days from the original. We can use the built-in standard SQL Server functions in all these scenarios to manipulate the Date.
The following list of the table will show you the available list of Functions. Use these links to learn Sql Server data functions, which include getting data, formatting datetime, etc.
Higher-Precision SQL Date and Time Functions
The following are the high-precision functions in SQL Server.
| Function | 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) | The datetime along with the time zone offset of the system on which the current SQL Server instance is running. |
| SysUTCDateTime | SYSUTCDATETIME() | 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 data and time functions.
| Function | Syntax | Return Data Type | Description |
|---|---|---|---|
| Current_TimeStamp | CURRENT_TIMESTAMP | datetime | It returns the datetime of the system on which the current Server instance is running. |
| GetDate | GETDATE() | datetime | Returns the datetime of the computer on which the Server instance is running. |
| GetUTCDate | GETUTCDATE () | datetime | Return datetime of the system on which the 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.
| Function | Syntax | Return Data Type | Description |
|---|---|---|---|
| DateName | DATENAME (datepart, dt) | NVARCHAR | Returns the specified datename from the user specified. |
| DatePart | DATEPART (datepart, dt) | int | Returns the specified datepart from the user given. |
| DAY | DAY(dt) | int | Returns the Day part. |
| Month | MONTH(dt) | int | The Month part of the user-specified. |
| YEAR | YEAR(dt) | int | It returns the year part of the user-specified. |
Gets Date and Time Values from their Parts
The below SQL functions will return the date and time from the user-specified parts or constructing datetime.
| Function Name | Syntax | Return Datatype | Description |
|---|---|---|---|
| DateFromParts | DATEFROMPARTS (year, month, day) | date | It returns the Date value from the 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 | datetime value from given parts. |
| DateTimeOffsetFromParts | datetimeoffsetfroomparts (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) | datetime (precision) | 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, minutes, seconds, fractions, precision) | time (precision) | It returns a Time value with specified precision. |
SQL Functions to Get Date and Time Difference
The Following functions will help you to find the differences between the two dates.
| Name | Syntax | Data Type | Description |
|---|---|---|---|
| DateDiff | DATEDIFF (datepart, startdate, enddate) | int | It will calculate the difference between the start and end date. |
| DateDiff_Big | DATEDIFF_BIG (datepart, startdate, enddate) | bigint | Same as DATEDIFF, but it returns bigint datatype. |
Functions to modify Date and Time Values
The below SQL Server functions help you to alter or modify the user-specified date.
| Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| DateAdd | DATEADD(datepart, number, dt) | The data type of date argument | This date function returns a new datetime value by adding a 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 a 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 a fractional precision of the first argument. | It transforms the datetime2 value into datetimeofset value. |
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.
| Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| @@DateFirst | @@DATEFIRST | tinyint | 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 in use. |
| 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 formats. |
Validate Date and Time Values
We can use the below-shown SQL Server Date functions to validate the given value.
| Name | Syntax | Return Data Type | Description |
|---|---|---|---|
| ISDATE | ISDATE(Expression) | int | It determines whether the input expression is valid or not. |
