01 Introduction to date and time formats in SQL 02 Understanding the different date and time data types 03 Converting date formats using the CONVERT function 04 Converting time formats using the CAST function 05 Using the FORMAT function for custom date and time formatting 06 Handling time zone conversions with AT TIME ZONE What is UTC? 07 Converting between UTC and local time zones 08 Handling daylight saving time changes with DATEADD and DATEDIFF What is DST (daylight saving time)? 09 Best practices for storing and manipulating dates and times 10 Conclusion: Mastering date and time conversions in SQL
01 Introduction to date and time formats in SQL
Microsoft SQL Server (MSSQL) supports a variety of date and time formats for storing and manipulating date and time values in the database. The following are the most commonly used formats:
YYYY-MM-DD
This format represents the date in the year-month-day format, with YYYY representing the year, MM
representing the month, and DD representing the day.
HH:MM:DD
This format displays the time in the hour-minute-second format, where HH represents the hour in 24-hour
clock format, MM represents the minute, and SS represents the second.
YYYY-MM-DD HH:MM:SS
The date and time are represented in the year-month-day hour-minute-second format, with the date separated
by a space from the time.
YYYY-MM-DDTHH:MM:SS
This is the ISO standard format for representing date and time values, with the "T" separating the date from
the time.
YYYY-MM-DD HH:MM:SS.nnnnnnn
This format represents the date and time to seven decimal places of precision, with the last seven digits
representing fractional seconds.
YYYY-MM-DD HH:MM:SS.mmm
This format represents the date and time with three decimal places of precision, with the last three digits
representing fractional seconds.
MM/DD/YYYY HH:MM:SS
This format represents the date and time in the month/day/year hour:minute:second format, with forward
slashes separating the month, day, and year.
It is important to note that the default date and time format in MSSQL may differ depending on system settings and regional configurations. However, standard date and time formats are always recommended for better compatibility and consistency across different systems and applications.
02 Understanding the different date and time data types
Microsoft SQL Server (MSSQL) supports a variety of data types for storing date and time values, each with its own format and range of values. The following are the most commonly used data types:
DATE
This data type only stores the date in the format YYYY-MM-DD.
The values range from January 1, 0001 to December 31, 9999.
TIME
This data type only stores the time component in the format hh:mm:ss.0000000.
The value range is 00:00:00.0000000 to 23:59:59.9999999.
DATETIME
This data type stores date and time information in the format YYYY-MM-DD hh:mm:ss.000.
The values range from January 1, 1753 to December 31, 9999.
SMALLDATETIME
This data type stores both date and time components in the YYYY-MM-DD hh:mm:ss format.
The values range from January 1, 1900 to June 6, 2079.
DATETIME2
This data type stores date and time components in the format YYYY-MM-DD hh:mm:ss.ffffff.
The values range from January 1, 0001 to December 31, 9999.
DATETIMEOFFSET
This data type stores both date and time components in the following format: YYYY-MM-DD hh:mm:ss.ffffff
+hh:mm
or -hh:mm.
The values range from January 1, 0001 to December 31, 9999.
SMALLDATETIME
This data type only stores the time portion with a resolution of one-tenth of a second.
The value range is 00:00:00.0000000 to 23:59:59.9999999.
To ensure accurate representation and manipulation of data when storing and retrieving date and time values in MSSQL, it is critical to understand the data types and their specific formats and ranges.
03 Converting date, time and datetime formats using the CONVERT function
The CONVERT function in Microsoft SQL Server (MSSQL) can convert date and time values from one format to another. The CONVERT function takes three arguments: the target data type, the expression to be converted, and an optional style code.
The syntax of the CONVERT function is as follows:
CONVERT ( data_type [ ( length ) ], expression, [ style ] )
Here are some examples of how the CONVERT function can be used to convert date and time values to different formats:
SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS 'YYYY-MM-DD' ---- 2023-03-19
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS 'YYYY-MM-DD HH:MM:SS' ---- 2023-03-19 15:43:33
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS 'MM/DD/YYYY' ---- 03/19/2023
SELECT CONVERT(VARCHAR(24), GETDATE(), 126) AS 'ISO' ---- 2023-03-19T15:43:33.057
SELECT CONVERT(VARCHAR(8), GETDATE(), 108) ---- 15:43:33
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) ---- 15:43:33:057
In the preceding examples, the second argument of the CONVERT function is the expression to be converted, which is typically a date or datetime value. The third argument is the style code, which specifies the target format of the conversion. The first argument of the CONVERT function specifies the target data type and length.
The CONVERT function in Microsoft SQL Server (MSSQL) supports a variety of style codes for converting date and time formats. The style codes specify the format of the input date and time value as well as the output format.
Here are some of the most commonly used style codes in MSSQL for date and time formats:
- 101: MM/DD/YYYY
- 102: YYYY.MM.DD
- 103: DD/MM/YYYY
- 104: DD.MM.YYYY
- 105: DD-MM-YYYY
- 106: DD MMM YYYY
- 107: MMM DD, YYYY
- 108: HH:MM:SS
- 109: MMM DD YYYY HH:MM:SS:MMM
- 110: MM-DD-YYYY
- 111: YYYY/MM/DD
- 112: YYYYMMDD
- 113: DD MMM YYYY HH:MM:SS
- 114: HH:MM:SS.MMM
- 120: YYYY-MM-DD HH:MM:SS
- 121: YYYY-MM-DD HH:MI:SS.MMM
- 126: ISO8601 format (YYYY-MM-DDTHH:MM:SS.mmmZ)
04 Converting time formats using the CAST function
The CAST function in Microsoft SQL Server (MSSQL) can be used to convert time values from one format to another. The CAST function is used to convert one data type's value to another. When converting time values, the time value must be stored in a data type such as datetime, smalldatetime, or datetimeoffset.
The CAST function's syntax for converting time values is as follows:
CAST ( expression AS data_type )
Here are some examples of how to use the CAST function to convert time values to different formats:
SELECT CAST(GETDATE() AS TIME) AS 'HH:MM:SS' ---- 22:22:34.1733333
SELECT CONVERT(VARCHAR(5), CAST(GETDATE() AS TIME), 108) AS 'HH:MM' ---- 22:22
The expression argument in the preceding examples is the time or datetime value to be converted. The data type argument specifies the target data type to which the value should be converted. The data type in this case is TIME, which converts the value to the time data type.
When you use the CAST function to convert a datetime value to a time value, only the time portion of the datetime value is preserved. Furthermore, the format of the output value may be affected by system settings and regional configurations.
If you want to convert time values to a specific format, as previously discussed, use the CONVERT function with the appropriate style code.
05 Using the FORMAT function for custom date and time formatting
You can format dates and times in Microsoft SQL Server using the FORMAT function. Here's an example of how to format a date in a custom format using the FORMAT function:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS 'CustomFormattedDate' ---- 2023-03-17
You can also use the FORMAT function to format times in a specific way. Here's an example of how to format a time in a custom format using the FORMAT function:
SELECT FORMAT(GETDATE(), 'hh:mm:ss tt') AS 'CustomFormattedTime' ---- 05:53:04 AM
Here are some examples of common format specifiers:
- yyyy: Four-digit year.
- MM: Two-digit month.
- dd: Two-digit day of the month.
- hh: Two-digit hour (12-hour clock).
- HH: Two-digit hour (24-hour clock).
- mm: Two-digit minute.
- ss: Two-digit second.
- tt: AM/PM designator.
These format specifiers can be combined with other characters to create a custom format. As an example:
- yyyy-MM-dd: ISO 8601 date format.
- MM/dd/yyyy: US date format.
- hh:mm:ss tt: 12-hour clock time format with AM/PM designator.
- HH:mm:ss: 24-hour clock time format.
Note that the FORMAT function is only available in SQL Server 2012 and later versions.
06 Handling time zone conversions with AT TIME ZONE
To handle time zone conversions in Microsoft SQL Server, use the AT TIME ZONE function. This function converts a datetime value from the specified time zone to the local time zone or from the local time zone to the specified time zone.
Here's an example of how to convert a datetime value to a different time zone using the AT TIME ZONE function:
SELECT GETUTCDATE() AT TIME ZONE 'Pacific Standard Time' AS 'ConvertedTime' ---- 2023-03-18 13:27:49.830 -07:00
This will convert the current UTC time to Pacific Standard Time.
The AT TIME ZONE function can also be used to convert a datetime value from one time zone to another. As an example, consider the following:
DECLARE @DATETIME datetime = (SELECt CAST(GETUTCDATE() as datetime)) ---- 2023-03-18 13:51:36.843
SELECT @DATETIME AT TIME ZONE 'Central European Standard Time' ---- 2023-03-18 19:17:30.933 +01:00
SELECT @DATETIME AT TIME ZONE 'Eastern Standard Time' ---- 2023-03-18 19:17:30.933 -04:00
SELECT @DATETIME AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'Eastern Standard Time' ---- 2023-03-18 14:17:30.933 -04:00
The datetime value '2023-03-18 19:17:30.933' in the Central European Standard Time zone converted to Eastern Standard Time will be returned.
It is important to remember that the time zone names used in the AT TIME ZONE function must be valid Windows time zone names. Run the following SQL statement to get a list of valid time zone names:
SELECT * FROM sys.time_zone_info
Also, keep in mind that the AT TIME ZONE function is only available in SQL Server 2016 and later versions.
What is UTC?
UTC is an abbreviation for Coordinated Universal Time, which is a timekeeping system that is used as a global reference time. It's also referred to as "Zulu time" in the military and aviation communities. UTC is the primary time standard used by the world to regulate clocks and time. It is based on the time kept by atomic clocks. UTC is used to determine time zones all over the world, as well as for scientific and meteorological purposes. UTC, unlike local time, is unaffected by daylight saving time and remains constant throughout the year.
07 Converting between UTC and local time zones
To convert between UTC and local time zones in Microsoft SQL Server, use the functions CONVERT, SWITCHOFFSET, and TODATETIMEOFFSET.
Here's an example of converting a UTC datetime to local time:
SELECT CONVERT(datetime, SWITCHOFFSET(GETUTCDATE(), DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))) AS 'LocalTime' ---- 2023-03-18 19:35:47.160
The current UTC datetime value converted to the local time zone will be returned.
Here's an example of converting a local datetime to UTC:
DECLARE @LocalTime datetime = (SELECT GETDATE())
SELECT CONVERT(datetime, TODATETIMEOFFSET(@LocalTime, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))) AT TIME ZONE 'UTC' AS 'UtcTime' ---- 2023-03-18 19:40:59.723 +00:00
It is worth noting that the function SWITCHOFFSET modifies a datetime value's time zone offset, whereas the function TODATETIMEOFFSET adds a time zone offset to a datetime value. DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) returns the time zone offset from UTC that is used in the conversion.
Also, the CONVERT function is used to convert the datetime value to the correct data type. In the second example, the AT TIME ZONE function is used to convert the datetime value to UTC.
It is important to note that the SQL Server's local time zone may differ from the client's or application's local time zone. As a result, it's critical to use the correct time zone offset in the conversion.
08 Handling daylight saving time changes with DATEADD and DATEDIFF
When adding or subtracting time intervals in Microsoft SQL Server, you can use the DATEADD and DATEDIFF functions to account for daylight savings time changes.
Here's an example of how to add a time interval to a datetime value while accounting for daylight saving time changes:
DECLARE @StartTime datetime = '2022-03-08 19:55:45'
DECLARE @Interval int = 60
SELECT DATEADD(minute, @Interval, SWITCHOFFSET(TODATETIMEOFFSET(@StartTime, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())), '-07:00')) AS 'EndTime' ---- 2022-03-08 08:25:45.000 -07:00
This will add 60 minutes to the datetime value '2022-03-08 19:55:45' to account for the daylight saving time change in the US Pacific time zone on March 8, 2022. The SWITCHOFFSET function is used to convert the datetime value to the Pacific time zone with a daylight saving time offset of -07:00.
Here's an example of how to calculate the difference between two datetime values while accounting for daylight saving time changes:
DECLARE @StartTime datetime = '2022-11-06 01:00:00'
DECLARE @EndTime datetime = '2022-11-06 02:00:00'
SELECT DATEDIFF(minute, SWITCHOFFSET(TODATETIMEOFFSET(@StartTime, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())), '-08:00'), SWITCHOFFSET(TODATETIMEOFFSET(@EndTime, DATEPART(TZOFFSET, SYSDATETIMEOFFSET())), '-07:00')) AS 'Interval' ---- 60
This will compute the difference in minutes between the datetime values '2022-11-06 01:00:00' and '2022-11-06 02:00:00' in the US Pacific time zone on November 6, 2022. The SWITCHOFFSET function is used to convert the datetime values to the correct time zone and offset.
When converting datetime values to account for daylight saving time changes, it is critical to use the correct time zone offset. The offset varies according to the date and time zone.
What is DST (daylight saving time)?
Daylight Saving Time (DST) is a summertime practice of advancing the clock by one hour to provide more daylight during the evening hours. This is accomplished by advancing the clock by one hour in the spring, typically on the second Sunday in March, and resetting it by one hour in the fall, typically on the first Sunday in November. The idea behind DST is to make better use of available daylight and reduce the amount of electricity required for evening lighting. DST is not observed in all countries, and those that do may have different start and end dates depending on their location and climate.
09 Best practices for storing and manipulating dates and times
Best practices for storing and manipulating dates and times in Microsoft SQL Server are as follows:
Use the appropriate data types:
For date and time values, use the DATE, TIME, DATETIME, DATETIME2, or SMALLDATETIME data types depending on
the
precision needed. Avoid using the deprecated DATETIMEOFFSET data type in new development.
Use UTC for internal storage:
To avoid confusion and inconsistencies caused by time zone differences, store date and time values in UTC
format in the
database. Only convert to local time when needed for display or user input.
Use system functions for date and time manipulation:
Instead of writing custom code, use built-in functions like DATEADD, DATEDIFF, CONVERT, and CAST to
manipulate date and
time values. These functions have been optimized for speed and accuracy.
Use explicit data type conversion:
Avoid using implicit data type conversion, as this can lead to unexpected results or errors. To explicitly
convert date
and time values to the desired data type, use the CONVERT or CAST functions.
Handle daylight saving time changes:
When manipulating date and time values, take into account changes in daylight saving time, especially when
adding or
subtracting intervals. To handle time zone changes, use functions like SWITCHOFFSET and TODATETIMEOFFSET.
Validate user input:
Validate user input for date and time values to ensure proper format and range. To prevent incorrect or
malicious input,
use input masks, range validation, and error handling.
Use datetime2 data type for high-precision time values:
Use the DATETIME2 data type instead of the DATETIME data type if you need to store time values with high
precision
(beyond the millisecond level).
Use computed columns for calculated date and time values:
Instead of storing calculated date and time values in the database, use computed columns to store them. This
can help to
improve performance while also lowering storage requirements.
By following these best practices, you can ensure that your date and time values are stored and manipulated in Microsoft SQL Server accurately and efficiently.
10 Conclusion: Mastering date and time conversions in SQL
Understanding date and time conversions in Microsoft SQL Server is critical for storing and manipulating time-related data accurately. When working with date and time values, keep the following key concepts in mind:
- Understanding the distinction between UTC and local time zones, as well as the necessity of converting between them when necessary.
- To store time values with high precision, use appropriate data types such as DATETIME2.
- DATEADD, DATEDIFF, SWITCHOFFSET, and TODATETIMEOFFSET are used to handle changes in daylight saving time.
- Using system functions rather than custom code to manipulate date and time values.
- Validating user input to ensure it is in the appropriate format and range.
You can ensure that your data is accurate, consistent, and easy to work with by following best practices for storing and manipulating date and time values in Microsoft SQL Server.