SQL Server Date Functions
The date and time function allows us to handle date and time data effectively. While working with the database, the format of date and time functions should be matched while inserting data into the table. In this article, we’ll go through the date and time functions in depth. SQL Server has a number of built-in functions that assist us in filtering useful data from a vast quantity of data. It is also useful in designing and maintaining a large-scale database.
We have a variety of data types in SQL Server that can be used as the date in our table. The most popular format of date is ‘YYYY-MM-DD’ and ‘DD-MM-YYYYY.’. In some circumstances, we also need to save time with the date in our database. In these cases, we require tools to access the time and date separately. This is where SQL Server’s time and functions are useful. It is also recommended that the beginner be cautious when using date or time in the database, as these are prone to throwing exceptions if not handled correctly.
Format of Date and Time in SQL Server
The following are the formats of date and time used in the SQL Server:
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH: MI: SS
TIMESTAMP: YYYY-MM-DD HH: MI: SS
YEAR: YYYY or YY
NOTE: We choose the date data types for each column while creating a new table in the database.
Why do we need date and time functions?
In SQL Server, we have a variety of date and time functions. These are included to ensure that the date and time module is accessible while creating and accessing a database.
SQL Server categorises the date and time functions based on the following types:
- Returning the System Date and Time Values
- Returning the Date and Time Parts
- Returning the Date and Time Values from Their Parts
- Returning the Date and Time Difference Values
- Returning the Modify Date and Time Values
- SET or Return Session Format Functions
- Returning the Validate Date and Time Values
Returning the System Date and Time
All system date and time values are derived from the computer’s operating system on which SQL Server is installed. The below tables explains all system date and time functions:
| Function | Descriptions |
|---|---|
| CURRENT_TIMESTAMP | This function is used to get the current date and time values without including the time zone offset. |
| GETUTCDATE | This function is used to get the current UTC date and time values as an integer. |
| GETDATE | This function is used to get the system’s current date and time on which the SQL Server is installed. |
| SYSDATETIME | This function is used to get the system’s current date and time with more fractional second precision without including the time zone offset. |
| SYSUTCDATETIME | This function is used to get the system’s current date and time value based on the UTC timestamp as an integer. |
| SYSDATETIMEOFFSET | This function is used to get the system’s current date and time with the time zone offset. |
Let us see some of the examples of the system date and time functions.
Example1: This example uses the CURRENT_TIMESTAMP() function to see the system’s current date and time without considering the timezone in the output:
This statement returns the below output:
Example2: This example uses the GETDATE() function to see the system’s current date and time on which the SQL Server is running:
This statement returns the below output:
Example3: This example uses the GETUTCDATE() function to see the system’s current date and time based on the UTC timestamp as an integer:
This statement returns the below output:
Example4: This example uses the SYSDATETIME() function to see the system’s current date and time with more fractional precision in milliseconds:
This statement returns the below output:
Example5: This example uses the SYSUTCDATETIME() function to see the system’s current date and time based on the UTC timestamp as an integer:
This statement returns the below output:
Example6: This example uses the SYSDATETIMEOFFSET() function to see the system’s current date and time with the timezone offset:
This statement returns the below output:
Returning the date and time parts
The date and time functions listed in the below table are used to extract a portion of the date and time in terms of the day, month, year, hour, minute, second, week, nanoseconds, etc.
| Function | Descriptions |
|---|---|
| DATENAME | This function is used to get a portion of the date in day, month, or year as a character string. |
| DATEPART | This function is used to get the portion of the date as an integer number. |
| DAY | This function is used to get the day value from the input dates as an integer. |
| MONTH | This function is used to get the month value from the input dates as an integer. |
| YEAR | This function is used to get the year value from the input dates as an integer. |
The following examples illustrate these date functions practically.
Example1: This example uses the DATENAME() function to extract the part of the date such as day, month, or year.
Executing the statement will return the below output. Here we see that the function extract the dates in three parts day, month, and year as a character string:
Example2: This example uses the DATEPART() function to extract the part of the date as an integer value, which makes it different from the DATENAME() function.
Executing the statement will return the below output. Here we see that the function extract the dates in three parts day, month, and year in the integer form:
Example3: This example uses the YEAR() function to extract only the year portion of the input dates.
Executing the statement will return only the year portion of the date in the integer form:
Example4: This example uses the MONTH() function to extract only the month portion of the input dates.
Executing the statement will return only the month portion of the date in the integer form:
Example5: This example uses the DAY() function to extract only the day portion of the input dates.
Executing the statement will return only the day portion of the date in the integer form:
Returning the Date and Time Difference Values
The function listed in the below table is used to extract the differences between dates:
| Function | Descriptions |
|---|---|
| DATEDIFF | This function is to get the difference in a date part of the two input dates values. |
Example: This example uses the DATEDIFF() function and displays the differences between the starting and ending date expressions.
Executing the statement will return the below output:
Returning the Modify Date and Time Values
We can use the following set of SQL Server Date functions to manipulate or change the system/input timestamp values.
| Function | Descriptions |
|---|---|
| DATEADD | This function is used to add an integer value to a date part of the input dates and returns the new date value. |
| EOMONTH | This function is used to get the last day of the month with the specified date and an optional offset. |
| SWITCHOFFSET | This function is used to modify the timezone offset of a datetime offset value and preserves the UTC value. |
| TODATETIMEOFFSET | This function is used to change the DATETIME2 value into a DATETIMEOFFSET value. |
The following examples illustrate these date functions practically.
Example1: This example uses the DATEADD() function and returns a new date value after adding an integer value to the date portion.
Executing the statement will return the below output. Here we see that the function first add a second value in the result1 and then add a day value in the result2 to display the next date:
Example2: This example uses the EOMONTH() function and returns the last day of the month for a specified date.
Executing the statement will return the below output. Here we see that the function still returns the correct output if we pass the date of a leap year.
Example3: This example uses the SWITCHOFFSET() function to change the stored timezone offset value into a new timezone offset.
First, we will create a table ‘testdate’ that contains the DATETIMEOFFSET column and insert a value into them using the below statement:
If we verify with the SELECT statement, it shows the ‘2020-09-20 07:45:50.7134500 -05:00’ value in the table. Now, we will use the SWITCHOFFSET() function to switch the timezone to -8:00:
We will get the below output:
Example4: This example uses the TODATETIMEOFFSET() function and returns a new date value after the DATETIME2 value in local time for a specified timezone.
Executing the statement will return the below output. Here we see that the first result changes the zone offset of a date and time to the zone -08:00. The second result changes the current timezone to -180 minutes:
Constructing date and time from their parts
We can use the following date function in SQL Server to construct the date and time from their parts.
| Function | Descriptions |
|---|---|
| DATEFROMPARTS | This function is used to get a date value from the specified day, month, or year. |
| DATETIME2FROMPARTS | This function is used to get a DATETIME2 value from the date and time arguments. |
| DATETIMEOFFSETFROMPARTS | This function is used to get a DATETIMEOFFSET value from the date and time arguments. |
| TIMEFROMPARTS | This function is used to get a time value from the time parts with precision. |
Let us explain these functions with the help of examples practically.
Example1: This example uses the DATEFROMPARTS() function to construct a date from the day, month, and year values.
Executing the statement will show the below output. Here we see the result1 return the date, but result2 returns NULL as the month argument is NULL.
Example2: This example uses the DATETIME2FROMPARTS() function to construct a datetime2 value from the day, month, year, hour, minute, seconds, fractions, and precision values.
Executing the statement will show the below output. Here we see the result1 return the datetime value, but result2 returns NULL as the month argument is NULL.
Example3: Example3: This example uses the DATETIMEOFFSETFROMPARTS() function to construct a datetimeoffset value from the date and time values.
Executing the statement will show the below output. Here we see the result1 return the datetimeoffset value, but result2 returns NULL as the year argument is NULL.
Example4: This example uses the TIMEFROMPARTS() function to construct a time value for the specified time and precision values.
Executing the statement will show the below output. Here we see the result1 return the time value without fraction, but result2 returns the time with fractions. The fraction value is calculated as 5/100 or 0.05 of a second as the given precision is 2.
Validation of Date and Time values
We can use the below date function to check whether the specified date format is valid or not in SQL Server. The valid date format is yyyy-mm-dd.
| Function | Descriptions |
|---|---|
| ISDATE | This function is used to check the entered dates follows the standard format of date, time, or datetime value or not. |
Example: This example uses the ISDATE() function to check whether the entered date follows the standard format or not. If the date is valid, the function returns 1. If the date format differs, the function returns 0.
Executing the statement will show the below output. Here we see the result1 represents the valid date data as it return1. The result2 represents the invalid date as it returns 0.
It is another example that checks the valid datetime expression:
Here is the output:
Set or Return Session Format Functions
We can use the below functions to set the date and time value for the specific session.
| Function | Descriptions |
|---|---|
| @@DATEFIRST | This function is used to get the current value of SET DATEFIRST for the session. |
| SET DATEFIRST | This function is used to sets the week’s first day to a number between 1 and 7. |
| SET DATEFORMAT | This function is used to determine the order of the dateparts (month, day, and year) for entered datetime or smalldatetime data. |
| @@LANGUAGE | This function is used to get the language name current used. |
| SET LANGUAGE | This function is used to set the language for the system and system messages. |
The following examples explain these functions practically.
Example1: This function uses the @@DATEFIRST() function to specify the first day of the week.
Executing the statement will set TUESDAY as the first weekday.
Example2: This function uses the DATEFORMAT() function to specify the order of the date.
Executing the statement will return the below output.
Conclusion
This example will explain a complete overview of date and time functions with examples. These functions are pre-defines in SQL Server that can be used to extract any detail we require in a query.