So far, we haven’t talked about SQL Server date and time functions. Today we’ll change that. We’ll take a close look at the ones most frequently used and mention all other date and time functions as well. This will be also the first step to create reports, including date and time functions. We’ll do that in upcoming articles in this series.

Data model and some general thoughts

The data model we’ll use is the same one we’re using in this series. We won’t actually use any data from the tables. I just want to point out that we’re using date and datetime data types in our model. Some of them are the result of storing the real-life data (next_call_date), while others are generated automatically by the system when data is inserted (ts_inserted). This shall usually be the case in most models as well.

We’ve mentioned that we’ll talk about SQL Server date and time functions. As their name says, they are functions and they work the same way as user-defined functions work. You can read more about it in this article. If we would like to explain this in the simplest possible manner, a function takes a number of parameters (could be none, 1 or more than 1) and returns the value. A function won’t have parameters only if it returns a system value (reading from a predefined source). Some of these functions will transform date & time data types into integers or strings, while others will do the opposite. Also, there will be format changes between different date and time data types.

SQL Server date and time data types

Before moving to functions, we’ll need to mention the date and time data types these functions work with. As this is the case with all data types, they are just a different way SQL Server interprets the set of 0s and 1s stored in the memory. This interpretation allows us to store different types of information with different precision. This happens when we’re talking about numbers, times, more generally about values that are stored as decimal numbers in real life. Since you can insert another decimal number between any two decimal numbers, each such value is indefinite and we need to make an approximation.

• Note: The number of bytes (bits) used for a certain data type determines the range of possible values we can store, as well the precision for types which are used to approximate decimal number

Let’s now list all SQL Server date and time data types (starting with the most commonly used):

• date – format is YYYY-MM-DD; stores values from 0001-01-01 to 9999-12-31; with the accuracy of 1 day (there is no approximation here because acts same as integer values); uses 3 bytes
• datetime –format is YYYY-MM-DD hh:mm:ss[.nnn]; stores values from 1753-01-01 to 9999-12-31; with the accuracy of 0.00333 seconds (please notice we have approximation here); uses 8 bytes
• time – format is hh:mm:ss[.nnnnnnn]; stores values from 00:00:00.0000000 to 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 3 to 5 bytes
• smalldatetime – format is YYYY-MM-DD hh:mm:ss; stores values from 1900-01-01 to 2079-06-06; with the accuracy of 1 minute; uses 4 bytes
• datetime2 –format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 6 to 8 bytes
• datetimeoffset – format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 8 to 10 bytes

In most cases, you’ll use either datetime, either date. The remaining 4 types are here if you want to have higher accuracy (datetime2, datetimeoffset), lower accuracy (smalldatetime), or store only time (time).

Frequently used SQL Server date and time functions

Similarly to date and time data types, some SQL Server date and time functions are used more often, while some are used rarely. In this part, we’ll check these that are used often, and you can expect you’ll need them in many situations.

If you want to get system values, you’ll use some of the following:

GETDATE() and CURRENT_TIMESTAMP return the datetime value from the server where SQL Server runs.

SYSDATETIME() returns the same as the previous 2, but with the greater precision, so the result returned is of the datetimeoffset(7).

Besides system date and time functions, we have several other important functions.

The next important set of functions is the one containing functions that return date parts. Let’s take a look at the following statements and their result.

YEAR(…), MONTH(…), and DAY(…) return related parts of the given date.

DATEPART(date_part, date) and DATENAME(date_part, date) both return parts of the date. DATEPART returns them as integer values, while DATENAME returns them as strings. Please notice that you can define more than just a year, month or day. All date_part values you can define are: year(yy, yyyy); quarter(qq, q); month(mm, m); dayofyear(dy, y); day(dd, d); week(wk, ww); weekday(dw); hour(hh); minute(mi, n); second(ss, s); millisecond(ms); microsecond(mcs); nanosecond(ns); TZoffset(tz); ISO_WEEK(isowk, isoww).

The next 3 functions are used to create date or modify/combine dates. Let’s take a look at them.

DATEFROMPARTS(year, month, day) takes a year, month and day as integer values and creates 1 date out of them.

DATEADD(date_part, interval, date) takes 3 arguments and returns a date that is interval (date_parts) number of given units (date_part) distant from the given date (date).

DATEDIFF(date_part, start_date, end_date) returns the number of units (date_part) between end_date and start_date (end_date – start_date).

This is the end of my selection of the most commonly used SQL Server date and time functions. These functions should solve most of your “problems”. Still, there are some more, and we’ll cover them now.

Less frequently used SQL Server date and time functions

Let’s mention the less frequently used SQL Server date and time functions.

We’ll start with GETDATE() and SYSDATETIME() counterparts. Let’s take a look at the following statements:

GETUTCDATE() acts the same as GETDATE() and CURRENT_TIMESTAMP but it returns the UTC datetime value.

SYSUTCDATETIME() acts the same as SYSDATETIME() but it returns UTC values.

SYSDATETIMEOFFSET() is the same as SYSUTCDATETIME() but besides it also returns the time zone offset.

• Note: This note is completely unrelated to date and time functions. Please notice that in the first 2 statements I haven’t used AS, while in 3^rd I did it – with the same output (alias name had been used)

Another interesting function is EOMONTH.

For the given date, it returns the last date in this month. This proves to be very useful in several situations.

I’ll list the remaining functions without giving examples:

• SWITCHOFFSET – Preserves the UTC value while changing the time zone to the one of a DATETIMEOFFSET value
• TODATETIMEOFFSET – Changes type from DATETIME2 into a DATETIMEOFFSET
• DATETIME2FROMPARTS – Creates and returns the DATETIME2 from the given date and time parts
• DATETIMEOFFSETFROMPARTS – Creates and returns the DATETIMEOFFSET from the given date and time parts
• TIMEFROMPARTS – Does the same as what DATEFROMPARTS does for the date, but for time. So, it creates a TIME from the given parts
• ISDATE – Is used to check if a given value is a valid datetime, date, or time, value

Conclusion

The first thing we want to do while working with databases is to store our real-world (business) data. In most cases, they’ll contain date and time values. Still, we can also expect that we’ll need to store other date and time values, e.g. when data had been inserted or updated. Working with SQL Server databases without using SQL Server date and time functions is almost impossible. Therefore, store link to this article somewhere to remind yourself of these functions (in case you’re stuck while working with dates & times).

In the upcoming article, we’ll use functions presented in this article to create report categories and reports. Stay tuned!

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020