1. Functions that return system date and time
Welcome to the second chapter of this course.
The information presented here will be very useful, date and time functions being an important topic for databases. Storing data inside a database barely has any value without attaching a date or time dimension to it. And since there aren't many databases without date and time information, you should know how to manipulate them.
2. Common mistakes when working with dates and time
Working with time data types is sometimes challenging and can become a source of errors and frustrations. Some common reasons are:
Inconsistent date time formats or patterns. For example, entering a date in the format day-month-year, while the database expects month-day-year.
Arithmetic operations on date types sometimes produce a different result than what we would expect.
Date and time stored in different timezones prove to be painful to work with in some situations, if we don't handle them properly.
These unexpected results are not caused by poor data type management in the database engine, but by using the wrong type or function and expecting the right results.
Let's see how, when, and where we should use date and time functions.
3. Time zones in SQL Server
The results returned by date and time functions are usually in your local timezone.
If the functions contain the UTC abbreviation in them, the result represents the Coordinated Universal Time. This is the primary time standard, by which all time zones are based.
4. Functions that return the date and time of the operating system
We'll start with the functions that retrieve time information from the operating system, meaning the operating system installed on the same computer as the SQL Server.
Based on the degree of accuracy, these are split in two categories: high-precision, measured by fractional seconds precision, and the less exact functions.
The high-precision functions are:
SYSDATETIME(), which returns the computer's date and time, without timezone information.
SYSUTCDATETIME() returns the computer’s date and time as UTC.
SYSDATETIMEOFFSET(), returning the computer’s date and time, together with the timezone offset.
The less precise functions are:
GETDATE(), which returns the current date,
GETUTCDATE(), returning the date as UTC,
CURRENT_TIMESTAMP, which is equivalent with GETDATE(),the only difference being that CURRENT_TIMESTAMP is called without any parameter, so without the parentheses.
5. Higher-precision functions example
This is an example of querying the high-precision functions
and
what their output looks like.
Notice the difference of 3 hours between UTC time and SYSDATETIME(). This is because of my local timezone. If you execute this query, you will get results specific to your location.
SYSUTCDATETIMEOFFSET() provides the local time including the difference from UTC in hours.
6. Lower-precision functions example
This is a query containing the lower-precision functions.
CURRENT_TIMESTAMP and GETDATE() are identical. GETUTCDATE() changes the local date to UTC.
These functions are similar to the previous ones, but they return a datetime type instead of datetime2, so their precision is not that accurate.
7. Retrieving only the date
If you want to return only the system date, you can use any of the system date and time functions, regardless of their precision, and explicitly convert the result to a date.
The results are the same, unless it's past midnight in one timezone and before midnight in UTC or the other way around.
8. Retrieving only the time
You can explicitly convert the result of these functions to time, if you want to select only the time part of a date.
The results are provided in local and UTC time, depending on which function is used.
9. Let's practice!
Let's now see how well you can put these functions to work.