The Microsoft SQL Server database stores the date and time information in a variety of formats. The most common of them are DateTime, DateTime2, and Date. As it happens with all types of data, issues can take place time after time. In this article, we’ll focus on troubleshooting some of the most common issues that you might face while working with the SQL time and date data types.
Issues Related to Regionally Different Date Formats
The format of the dates varies globally. For instance, the British write dates as dd-mm-yyyy, while Americans write dates in the mm-dd-yyyy format. This way, the same date, 31st December 2020, is written as 31-12-2020 in the British date format and as 12-31-2020 in the American format.
Incapability issues may arise if you do not specify the date in a format corresponding to the language settings of your SQL Server instance.
The following script converts the text string with the date information into the DATETIME format. The language settings are set to BRITISH. The text string contains 31-12-2020 04:25:30.
If you cast this string into the DATETIME format, 31 will be treated as day, while 12 will be month by default. Hence, the conversion will be successful as shown from the output:
SET LANGUAGE BRITISH;
DECLARE @date VARCHAR(50) = '31-12-2020 04:25:30';
SELECT CAST(@date AS DATETIME);
However, if you try to convert the string containing the date 31-12-2020 to the DATETIME format using the US_ENGLISH language settings, you will receive an error as shown below:
SET LANGUAGE US_ENGLISH;
DECLARE @date VARCHAR(50) = '31-12-2020 04:25:30';
SELECT CAST(@date AS DATETIME);
The error occurs because the US_ENGLISH language settings define 31 as a month instead of a day. As the month value cannot be greater than 12, we are receiving the out-of-range value error.
If you specify the date as 12-31-2020 and then convert the date string to DATETIME using the US_ENGLISH settings, you will see the successful conversion:
SET LANGUAGE US_ENGLISH;
DECLARE @date VARCHAR(50) = '12-31-2020 04:25:30';
SELECT CAST(@date AS DATETIME);
Similarly, converting the 12-31-2020 date string under the BRITISH language setting also causes an error – 31 is treated as a month, that can’t be.
SET LANGUAGE BRITISH;
DECLARE @date VARCHAR(50) = '12-31-2020 04:25:30';
SELECT CAST(@date AS DATETIME);
To convert your date accurately irrespective of the language settings, you can use the ISO standard 8601 for date format. To comply with this standard, specify the date as yyyy-mm-ddThh:mm:ss.
For example, the date string 2020-12-31T04:25:30 is successfully converted to the DATETIME data type under the BRITISH language settings:
SET LANGUAGE BRITISH;
DECLARE @date VARCHAR(50) = '2020-12-31T04:25:30';
SELECT CAST(@date AS DATETIME);
The following script shows the same string converted into DATETIME with the US_ENGLISH settings:
SET LANGUAGE US_ENGLISH;
DECLARE @date VARCHAR(50) = '2020-12-31T04:25:30';
SELECT CAST(@date AS DATETIME);
Time Zone Considerations
You may want to develop some SQL Server database applications for the global audience. For that, you may need to add the time zone information to the date and time data types.
In SQL Server the DATETIMEOFFSET data type stores the date and time information along with the time zone offset. The time zone offset is specified as UTC +/- number of hours.
For instance, the following script uses the SYSDATETIMEOFFSET() method to get the date, time, and offset information of the system running your SQL Server instance. The values returned by the SYSDATETIMEOFFSET() function are stored in the DATETIMEOFFSET type variable @dateoffset. The value of the @dateoffset variable is printed using the SELECT statement:
DECLARE @dateoffset DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT @dateoffset
The below output shows see the current date and time and the offset value. In this case, it is +02:00.
You can also get only the offset value from the DATETIMEOFFSET variable. To do so, you need to pass the DATETIMEOFFSET type variable as the second parameter value to the DATENAME() function. The first parameter to the DATENAME() method should be tzoffset.
The following script returns the time offset portion of the current system date:
DECLARE @dateoffset DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT DATENAME(tzoffset, @dateoffset)
To create a custom DATETIMEOFFSET variable, specify values for the date, time, and time offset portions. For instance, in the following script, the value for the date is 2015-02-22, the value for the time portion is 23:59:59:999, and the time offset value is +05:00.
DECLARE @dateoffset DATETIMEOFFSET = '2015-02-22 23:59:59:999 +05:00';
SELECT @dateoffset
Finally, you can also update the time offset information using the SWITCHOFFSET() function.
You need to pass the DATETIMEOFFSET type variable as the first parameter value and pass the new time offset as the second parameter value to the SWITCHOFFSET function.
The following script updates the time offset value for the DATETIMEOFFSET variable from +05:00 to +09:00.
DECLARE @dateoffset DATETIMEOFFSET = '2015-02-22 23:59:59:999 +05:00';
SELECT SWITCHOFFSET(@dateoffset, '+09:00');
Selecting Records Using BETWEEN Operator with DateTime
The BETWEEN operator in the SQL server filters the records between the range of values passed to it.
You can use the BETWEEN operator to return records between two dates. However, you should be extra careful while using it for filtering records with dates.
For instance, the following script creates a dummy Hostel database and adds one Student table to it.
CREATE DATABASE Hostel
USE Hostel
CREATE TABLE Student
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Gender VARCHAR (50),
BirthDate DateTime
)
The next script adds some dummy records to the Student table. The BirthDate column of the Student table stores dates. From this script, you can see that two students Sara and Nik have the same dates of birth. However, the time of birth is different:
INSERT INTO Student
VALUES ('Jack', 'Male', '2017-06-30 16:30:35'),
('Sara', 'Female', '2015-02-22 00:00:00'),
('Elisa', 'Female', '2020-03-16 22:24:39'),
('Nik', 'Male', '2015-02-22 09:45:55'),
('Jos', 'Male', '2015-03-25 11:55:20')
You would think that BETWEEN operator could be used to retrieve the records of all students born on 2015-02-22.
SELECT * FROM Student
WHERE BirthDate BETWEEN '2015-02-22' AND '2015-02-22'
But if you execute the above script, you will see that only one record is returned, despite the time part is also included.
The reason is that the BETWEEN operator treats by default the DATETIME value of 2015-02-22 as 2015-02-22 00:00:00. Therefore, the BETWEEN operator in the above query searched for the records with the BirthDate value between 2015-02-22 00:00:00 and 2015-02-22 00:00:00.
To solve this issue, we must specify the time portion when using the BETWEEN operator with the DATETIME data type.
The following script will return all records between 2015-02-22 00:00:00 and 2015-02-22 23:59:59:999. The time portion for the upper date limit is 23:59:999.
SELECT * FROM Student
WHERE BirthDate BETWEEN '2015-02-22' AND '2015-02-22 23:59:59:999';
In the output, we get two records for the BirthDate – 2015-02-22.
Issues Related to Date Ranges
The DATETIME data type supports only years 1753 through 9999. Therefore, if you try to store a date with a year value larger than 9999 or smaller than 1753, you will get an error.
The following script tries to convert the 1392-12-31 date string. 1392 is less than 1753. Hence, we have the out-of-range value error.
DECLARE @date VARCHAR(50) = '1392-12-31 04:25:30';
SELECT CAST(@date AS DATETIME);
To store year values less than 1753, you can use the DATETIME2 data type. It stores year values from 0000 to 9999.
The following script successfully converts the date string 1392-12-31 to DATETIME2 data type:
DECLARE @date VARCHAR(50) = '1392-12-31 04:25:30';
SELECT CAST(@date AS DATETIME2);
Using TRY_COVERT for DateTime Conversion
The CONVERT function in SQL Server converts the data from one type to another. You can use it for converting the date-type data formats to other formats and vice versa. However, if the conversion fails, the CONVERT function throws an error.
For instance, we are converting the string 2015-31-31 to DATETIME format:
DECLARE @date VARCHAR(50) = '2015-31-13';
SELECT CONVERT(DATETIME, @date ,105) as DOB_CONV
If you would like a NULL value returned when the conversion fails instead of the error message, use the TRY_CONVERT function. This method won’t let the application crash – it simply returns a NULL value.
DECLARE @date VARCHAR(50) = '2015-31-13';
SELECT TRY_CONVERT(DATETIME, @date ,105) as DOB_CONV
Conclusion
Working with SQL Server, you may face lots of issues spoiling your experience and complicating the tasks. Knowing the most common troubles is, on the other hand, the most efficient method to prevent them from happening. That’s why we dedicated this article to troubleshooting such inconveniences that may take place during your work with the information about dates and times.
Note also that modern tools for working with SQL Server databases can make the lives of DB specialists much simpler. In particular, the dbForge Studio for SQL Server provides the Visual Data Editor feature to apply when dealing with dates. You can use it to view and edit the dates in the most user-friendly manner.
Tags: date formats, datetime, sql date Last modified: September 16, 2021
