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:

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.

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:

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.

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.

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.

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.

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.