SQL Server Date, Datetime and Datetime2 - What should I use when I get the value as a string?

When we have to store a datatime value (or date), in most cases, application accepts the value as a datetime and send the value to SQL Server as a datetime value. However, if the value is sent as a string (Example, CSV upload), then what should be the best way of formatting the value and how we can convert it to datatime without making any mistake?

It is always recommended to use ISO 8601 standard when exchanging datatime values. The standard describes the way of passing a datetime value, generally it is YYYY-MM-DDTHH:MM:SS.sss. You can read more on this my post: Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III.

With SQL Server 2016, the default string format for dates is YYYY-MM-DD. If you pass the value with this format, regardless of the Current Language set, SQL Server will accurately read the value. However, this does not work with all datetime data types as expected. Have a look on the following code;

SET LANGUAGE English;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';

SELECT DATENAME(mm, @Date) As WithEnglish;
SELECT DATENAME(mm, @Datetime) As WithEnglish;
SELECT DATENAME(mm, @Datetime2)As WithEnglish;
GO


SET LANGUAGE German;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';


SELECT DATENAME(mm, @Date) As WithGerman;
SELECT DATENAME(mm, @Datetime) As WithGerman;
SELECT DATENAME(mm, @Datetime2) As WithGerman;

As you see, Datetime data type convert happens based on the language set but Date and Datetime2 data types are accurately interpreted regardless of the language set. This is something you have to remember. If you expect datetime values as string and settings related to the session can be changed, then it is always better to use either Date or Datetime2.

If you need to make sure that date is properly getting interpreted regardless of the settings (language), then stick into ISO 8601. If you change the values of variable as below, you will get the same month: May for both English and German.

SET @Date = '2017-05-06T00:00:00';
SET @Datetime = '2017-05-06T00:00:00';
SET @Datetime2 = '2017-05-06T00:00:00';

Note that ISO 8601 accepts a value like 24:00:00 for time for midnight but SQL Server does not support it.

SQL Server: Calculate TimeSpan using TSQL - dd:hh:mm:ss

While coding on my new project, wanted to show the duration between two DateTime values as a Time Span like Remaining Days : Remaining Hours : Remaining Minutes : Remaining Seconds (dd:hh:mm:ss).

There are multiple solutions available in the net and you can certainly use one of them as long as they satisfy your requirements. However, this is what I wrote, it may be useful if you need the same.

DECLARE @d1 datetime = '2016-12-24T00:44:01.000'
DECLARE @d2 datetime = '2016-12-25T01:43:00.000'

SELECT 
 CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) / 86400) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) END 
 + ':' +
 CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 86400) / 3600) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) END  
 + ':' +
 CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 3600) / 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) END 
 + ':' +
 CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) % 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) END 

Here are some examples used with above code;

SQL Server Brain Basher of the Week #010 - Best way to send datetime value

Exchanging data between systems is not an uncommon scenario and it has become a part of most of the business. When exchanging data, the structure and format of the output is sometime determined before exchanging or sometime destination just accepts as it comes. Transferring date/time values is always getting involved with these exchanges and in a way, it is the most common attribute that we frequently troubleshoot as it introduces issues during the transfer, hence thought to add something on it as this week's Brain Basher.

What is the best of sending a datatime value to another system?

There are many ways of transferring datetime values. Transfer it as a string value, as a numeric value based on a predetermined format, or a string value based on a predetermined format are some common methods. However, though we try to manage it our best, still there is a chance for identifying the datetime value incorrectly when transferring from one system to another. What is the solution for this? What if we can use a format that can be recognized by any database management system?

There is a way of passing datatime values formatted with a standard that can be recognized by almost all DBMSs. It is called ISO 8601 standard. Almost all system support functionalities for retreiveing stored data/time values under this standard. Once the value is received by the destination, destination system can recognize the pattern without misinterpreting the value. This is how it has to be formatted;

Here is a simple code that shows how to convert a datetime value to ISO 8601 and how to accept an ISO 8601 formatted value.

-- Converting to ISO 8601
SELECT CONVERT(char(23), GETDATE(), 126);

-- Retrieving ISO 8601 formatted value
DECLARE @StringValue varchar(30) = '2015-05-03T10:50:00.000'

-- This returns "May" recognizing middle part as the month
SELECT DateName(m, Convert(datetime, @StringValue));

Once I discussed this with one of the sessions hosted at SS SLUG. Here is the post I made on it: http://dinesql.blogspot.com/2013/12/best-way-to-pass-datetime-values-to-sql.html.

SQL Server Brain Basher of the Week #006

Let's talk about date and time. Have a look on following code and try to guess the value returned by last SELECT statement.

USE tempdb;
GO

CREATE TABLE dbo.TestTable
(
 Code char(4) NOT NULL
 , TimeRecorded datetime NOT NULL
)
GO

INSERT INTO dbo.TestTable 
 (Code, TimeRecorded)
VALUES 
 ('E001', '12/31/2014 23:59:59:999');
GO

SELECT TimeRecorded FROM dbo.TestTable;

The date and time we entered is a date related to 2014 but if you run the last SELECT statement, it will show a date for 2015, it will show the value as 2015-01-01 00:00:00.000.

How this can be happened? It can happen based on the "milliseconds" added to the value. What we have to remember is the Accuracy of DateTime data type. Accuracy of the datetime type is rounded to increments of .000, 003, or .007 seconds, hence .999 is rounded off to .000 increasing the second by 1. This changes the entire datetime value inserted. That is why we see 2014 date as 2015.

Considering this fact, if your application needs the accuracy at millisecond level, datetime2  should be used instead of datetime.

One more thing to remember. If you write a query like below to get some records related to 13th of June 2014 from a table;

SELECT Id
FROM dbo.Table
WHERE Date BETWEEN '06/13/2014 00:00:00:000' 
   AND '06/13/2014 23:59:59:999';

You might get records related to 14th of June too. Again the reason is same. It can be overcome by changing the query as below;

SELECT Id
FROM dbo.Table
WHERE Date >=  '06/13/2014 00:00:00:000' 
   AND Date < ‘06/14/2014 00:00:00:000’;

SQL Server Brain Basher of the Week #003

This week Brain Basher is based on data/time. See below code and try to guess the output of it.

DECLARE @datetime datetime
SET @datetime = '01/01/00'
SELECT YEAR (@datetime)

SET @datetime = '01/01/30'
SELECT YEAR (@datetime)

SET @datetime = '01/01/40'
SELECT YEAR (@datetime)

SET @datetime = '01/01/50'
SELECT YEAR (@datetime)

What did you guess? Years of 2000, 2030, 2040, and 2050? If so, it is wrong. Here is the output;

As you see, the last one is not 2050, it is 1950. This is based on a setting called two digit year cutoff. The setting represents the cutoff year for interpreting two-digits year as four-digits year and default value for this is 2049. This sets the default time span as 1950-2049, hence value of 49 represents, 2049 and value of 50 represents 1950. Another example is, value of 99 represents 1999 and value of 01 represents 2001.

It is not advisable to change this but this setting can be changed using sp_configure.

For more info: https://msdn.microsoft.com/en-us/library/ms191004.aspx

SQL Server DateTime data types, ranges, accuracy and default values

SQL Server offers different useful datetime data types for different purposes. Knowing all and their capabilities would definitely help you to decide the type to be used. Here are the most important properties of them. Click on the links for more information.

Data type	Range from	to	Default value	Accuracy
Date (1-3 bytes )	0001-01-01	9999-12-31	1900-01-01	One day
Datetime (8 bytes)	1753-01-01	9999-12-31	1900-01-01 00:00:00.000	Rounded to increments of .000, .003, or .007 seconds
Datetime2 (6-8 bytes)	0001-01-01	9999-12-31	1900-01-01 00:00:00.0000000	100 nanoseconds
datetimeoffset (10 bytes)	0001-01-01	9999-12-31	1900-01-01 00:00:00.0000000 +00:00	100 nanoseconds
Smalldatetime (4 bytes)	1900-01-01	2076-06-06	1900-01-01 00:00:00	One minute
Time (5 bytes)	00:00:00.0000000	23:59:59.9999999	00:00:00.0000000	100 nanoseconds

SQL Server: Different ways of getting current date and time

What is the usual way of getting current date and time from SQL Server? Obviously, the answer is GETDATE function. Do you know that there are 7 different ways of getting the same with bit differences? Here are the ways and their differences;

Function	Explanation
GETDATE()	Returns datetime.
GETUTCDATE()	Returns datetime in Universal Time Coordinated.
CURRENT_TIMESTAMP	Returns datetime. This is ANSI Standard function
SYSDATETIME()	Returns datetime2.
SYSUTCDATETIME()	Returns datetime2 in Universal Time Coordinated.
SYSDATETIMEOFFSET()	Returns datetimeoffset (time zone offset is included)
ODBC Canonical functions	Not a standard way but they can be used too. There are many functions and NOW() is similar to GETDATE() which returns datetime. This has to be called as; {fn NOW()}

1. SELECT GETDATE() [GETDATE]
2. SELECT GETUTCDATE() [GETUTCDATE]
3. SELECT CURRENT_TIMESTAMP [CURRENT_TIMESTAMP]
4. SELECT SYSDATETIME() [SYSDATETIME]
5. SELECT SYSUTCDATETIME() [SYSUTCDATETIME]
6. SELECT SYSDATETIMEOFFSET() [SYSDATETIMEOFFSET]
7. SELECT {fn NOW()} [NOW]

Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about different ways of passing datetime values to SQL Server databases.

Here is the question related to this demo;

There are many ways to pass datetime values to SQL Server. Some of ways are;

1. Value formatted as a string value
2. Value formatted as a datetime value
3. Value formatted as a datetime value and accepted via DATEFORMAT option
4. Value formatted as a ISO 8601 datetime value

What is the best way?

Majority uses the second option. It works fine as long as all applications are using correct datetime formats and all are in same country. However there could be misinterpretation if one of the application uses a different convention. Have a look on below C#.NET code (Note that the code written to show the demo only, it may not be the correct way to be written).

USE tempdb

GO

CREATE PROCEDURE dbo.ShowMonth @DateTimeValue datetime

    , @Month varchar(10) OUTPUT

AS

BEGIN

    SET @Month = DATENAME(m, @DateTimeValue)

END

GO

 

string year, month, date, datetime;

// making the datetime value from different string values

year = "2013";

month = "05";

date = "01";

datetime = month + "/" + date + "/" + year + " 00:00:00";

DateTime convertedDate = Convert.ToDateTime(datetime);

// set connection and command

SqlConnection connection = new SqlConnection(@"Server=SL-DEV001\SQL2012;Database=tempdb;uid=Dinesh;pwd=Dinesh");

SqlCommand command = new SqlCommand("dbo.ShowMonth", connection);

command.CommandType = CommandType.StoredProcedure;

SqlParameter parameterDateTime = new SqlParameter("DateTimeValue", SqlDbType.DateTime);

parameterDateTime.Value = convertedDate;

parameterDateTime.Direction = ParameterDirection.Input;

command.Parameters.Add(parameterDateTime);

SqlParameter parameterMonth = new SqlParameter("Month", SqlDbType.VarChar, 10);

parameterMonth.Direction = ParameterDirection.Output;

command.Parameters.Add(parameterMonth);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

Console.WriteLine(parameterMonth.Value.ToString());

When you execute the code, it returns the “month” as follows;

Now let’s change the “Culture” of the current thread. Add the below code as the first statement of C#.NET code.

Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");

And when the code is run, result is;

As you see, “month” returns from the stored procedure is now wrong. It means, Date has been misinterpreted by either application or SQL Server.

Now let’s change the way we pass the date and time as per ISO 8601;

//datetime = month + "/" + date + "/" + year + " 00:00:00";

datetime = year + "-" + month + "-" + date +  "T00:00:00";

If you execute the code now, SQL Server identifies the elements of passed value properly regardless of Culture setting and returns the value correctly. Therefore 4th option is the best way to pass datetime values to SQL Server.

ISO 8601
This is an international standard for exchanging dates and times. This standard provides an unambiguous and well-defined method of representing dates and times and avoids misinterpretation of dates and times.

Format of this standard separates date and time using “T” (uppercase). Year needs to be set with 4 digits, month needs to be set with 2 digits and, day needs to be set with 2 digits. Time sets in 24 hours format. Hour, minute and second are separated using “:” and fraction of second is separated using “.”.

For an example, “8-Jan-2013 2:45:44” as per ISO 8601 is “2013-01-08T14:45:44.000”.

When this is used with SQL Server, setting related to SET DATEFORMAT and SET LANGUAGE is ignored.

If you need to convert stored datetime values to ISO 8601 for exchanging them with another party, here is the way;

SELECT CONVERT(char(23), DateValue, 126)

You can use 127 instead of 126 if time zone information is required.

Read more on ISO 8601: http://en.wikipedia.org/wiki/ISO_8601#Dates

How SQL Server interprets two-digit years as four-digit years – SS SLUG Dec 2013 – Brain Bashers - Demo II

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses the way SQL Server interprets two-digit years to four-digit years.

Here is the question related to this demo;

You execute the following query. Note that the “years” of input values are sent as two-digit years.

DECLARE @datetime datetime

SET @datetime = '01/01/00'

SELECT YEAR (@datetime)

SET @datetime = '01/01/30'

SELECT YEAR (@datetime)

SET @datetime = '01/01/40'

SELECT YEAR (@datetime)

SET @datetime = '01/01/50'

SELECT YEAR (@datetime)

How SQL Server interprets each year to four-digit years?

In order to answer this question, you need to know the two digit year cutoff which SQL Server uses for determining the year. The default value of two digit year cutoff is 2049. If you pass a two-digit year that is less than or equal to last two digits of cutoff year (2049), SQL Server uses the same century as the cutoff year. Here is the result of above query;

Since the 50 is above cutoff year, it has been interpreted as 1950. All other two-digit years are set with century of the cutoff year.

You can change the cutoff year if required. This option appears only if “show advanced options” is enabled via sp_configure. If “show advanced options” is not enabled, enable it. Then, set the value you need for “two digit year cutoff”. The below code sets it as 2060.

-- enable advanced options

sp_configure 'show advanced options', 1

GO

reconfigure

-- set two digit year cutoff as 2060

sp_configure 'two digit year cutoff', 2060

GO

reconfigure

Let’s run the query again and see;

As you see, 50 is less than current cutoff year. Therefore even last value is taken as 2050. Change it back to 2049 to maintain the backward compatibility.

You need to know one more thing on this setting. If you are accepting year values via an OLE Automation object, you need to be aware that its cutoff year is 2030, not 2040. For an example, if a .NET application is used for sending values for a stored procedure and values are set as two-digit years, you may notice unacceptable years. Look at below code and see. If two-digit values are less than last two digit of cutoff year, it uses century of cutoff year.

DateTime dt;

dt = Convert.ToDateTime("01-01-00");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-30");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-40");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-50");

Console.WriteLine(dt.ToString());

Filtering DateTime values – SS SLUG Dec 2013 – SQL Brain Bashers - Demo I

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses a tricky (or error-prone) filtering issue with datetime values.

Here is the question related to this demo;

You have a table that contains two columns as below. First column type is int and second is datetime.

Id (int)	Date (datetime)
1	2010-06-12 09:17:19.320
2	2010-06-12 23:59:59.997
3	2010-06-13 00:00:00.000
4	2010-06-13 13:30:00.347
5	2010-06-13 23:59:59.997
6	2010-06-14 00:00:00.000
7	2010-06-14 00:00:00.420

You write a query for getting records related only to 2010-06-13;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date BETWEEN '06/13/2010 00:00:00:000' 

AND '06/13/2010 23:59:59:999'

Does this produce the required resultset?

Answer is “No”. Result includes 6th Id too, which is related to 2010-06-14.

The reason for this is, accuracy level of datetime data type and storage mechanism of datetime type. SQL Server only stores time to approximately 1/300th of a second (.00333 seconds), meaning time is stored as;

00:00:00:000
00:00:00:003
00:00:00:007

Because of this, when a value like “2013-12-25 10:30:50:995” is used, it gets rounded up to “2013-12-25 10:30:50:997”. In the query given with the example, “06/13/2010 23:59:59:999” is rounded up to “06/14/2010 00:00:00:000”, resulting 2010-06-14 record in the result.

However, this query can be written in a different way to get the required result;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date >=  '06/13/2010 00:00:00:000' 

    AND Date < '06/14/2010 00:00:00:000'

If you need to accuracy level of datetime for more than 0.00333 seconds, solution is, using datetime2 data type instead datetime. The accuracy of datetime2 data type goes up to 100 nanoseconds.

What is the correct way for setting values for datetime?

How do we make sure that we accept values for datetime columns correctly? Has the month stored as it has been sent? Has the day stored as it has been sent? As long as you control the client application and SQL Server modules, you are sure about SQL Server’s identification of each components of datetime value. What if SQL Server accepts values from third-party application? Will they always send the datetime value as you asked?

Here is an example. The variable @datetime accepts a value. Assume that you accept datetime values in mmddyyyy format. If SQL Server works on default setting, it will recognize the moth of value as July with first setting. If you get it in different format, as showed in second setting, SQL Server will take some other part as the month and store, which is wrong in this scenario.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '7/6/2010 2:30:00 pm'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

   5:  

   6: SET @datetime = '6/7/2010 2:30:00 pm'

   7: SELECT DATENAME(M, @datetime) AS MonthOfDate

We can force SQL Server to always read the values as mmddyyyy by setting the DATEFORMAT. But, again, if client application sends it in different format, there is a chance of storing wrong values.

   1: SET DATEFORMAT mdy

   2: GO

   3: DECLARE @datetime datetime

   4:  

   5: SET @datetime = '7/6/2010 2:30:00 pm'

   6: SELECT DATENAME(M, @datetime) AS MonthOfDate

   7:  

   8: SET @datetime = '6/7/2010 2:30:00 pm'

   9: SELECT DATENAME(M, @datetime) AS MonthOfDate

What would be the best way? Why do not we stick with international standard? Yes, SQL Server accepts datetime values in ISO 8601 format. If accept values from third-party application, we can ask them to send the values in ISO 8601 format. The format of it is YYYY-MM-DDThh:mm:ss.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '2010-07-06T14:30:00'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

If SQL Server receives values in ISO 8601 format, it ignores all datetime related format such as DATEFORMAT, LANGUAGE and accepts the first part as the Year, second part as the Month, and third part as the Day. Note that date and time part has to be separated with “T” and time format should be in 24-hour format.