en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
מאי4

Written by: ronen ariely
04/05/2015 17:36 RssIcon

* Error: “DateTime data type resulted in an out-of-range value”
* How can I change the format of a DateTime column in the database?

Background

There is a common confusion regarding "displaying format" and "storing format" of dates types. We can find lot of questions in the forums regarding "storing format" of dates, which are actually regarding the "displaying format", in external application.

For example questions like: I am getting the error “DateTime data type resulted in an out-of-range value”, or questions like: “how can I change the DateTime column format in the database”.

In this post I will I will focus on SQL DateTime type. I will organizes the issue using the different terms, and I will give some basic solutions for common issues.

General explanation

(1) When we convert between application variable and SQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API. (2) When we display a data from SQL Server in our application’s GUI, we are using simple text. formatted for display.

While we display dates in our application we use simple text. Writing dates in query is done using simple text. These text are written using displaying format that fit our application. For example we might use in one application the format ‘dd/MM/YYYY’ (‘27/02/2016’), and in another application we might use the format ‘YYYY-MM-dd’ (‘2016-02-27’). SQL Server do not use these formats for storing the data! In both examples the data stored in the database in the same format. These are only a displaying format.

* For more information regarding how SQL Server actually store the data you can check my next blogs. I intend to write several blogs regarding this issue for several types like DateTime, DateTime2, and more: Examine how DateTime2 type stored in the data file

SQL Server automatically implicit converts the data from one type to another, according to a built-in rules. Implicit conversions are not visible to the user.

For example, when simple text type like NVARCHAR passed to a DATETIME column or variable, the text is implicitly converted to DATETIME, before any action (comparison, insert to table, etc.).

Once the implicit conversion failed an error is raising. For example if SQL Server engine tried to convert 27 to month than we will get an error “out-of-range value”. Moreover, the implicit conversion might result with a wrong value. Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. These are the basic issues that we discuss in this blog.

Playing time - Examples

Most common example is when people try to use specific display format, which might work on one database but might raise an error on another one.

SELECT MyDate From MyTable WHERE MyDate = '01/02/03';

If the our text is ‘27/02/2016’, than we have no problem to understand that 2016 must be the year, and 27 must be the days, and therefor 02 is the month, but what if we will try to use the date ‘01/02/03’. Is that January second 2003, or February first 2003, or maybe this is March second 2001?

  

Example 01: Implicit convert

Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. Therefor using format that do not fit the language will raise an error.

SET LANGUAGE FRENCH;
GO
 
declare @D datetime = '2016-02-27' -- Implicit convert from text to DateTime
GO -- ERROR!!!
   -- La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
 
SET LANGUAGE us_english;
GO
 
declare @D datetime = '2016-02-27' -- Implicit convert from text to DateTime
GO -- OK


Example 02: Explicit convert without specific style

Using explicit convert without specified the style will act as implicit convert, since the SQL Server engine do not know what format to use.

SELECT CONVERT(DATETIME, '2009-10-13');
GO


Example 03: Explicit convert with correct style

Using explicit convert with the correct style will work, no matter what language we are using. This is the correct way to use the data!

SET LANGUAGE FRENCH;
GO
 
SELECT CONVERT(DATETIME, '2016-02-27',120); -- Explicit convert, using the style that fit LANGUAGE FRENCH
GO --OK
 
SET LANGUAGE us_english;
GO
 
SELECT CONVERT(DATETIME, '2016-02-27',120); -- Explicit convert, using the style that fit LANGUAGE FRENCH
GO --OK

  

Example 04: Implicit convert using generic format

We can use unambiguous unseparated format like ‘YYYYMMDD’ for dates and ‘YYYYMMDD hh:mm:ss’ for DateTime. These formats should work on any SQL Server database. Yet it will not guarantee you that it will work in any other database.

 

Important! DateTime class in Dot.Net does not behave like SQL Server DateTime type!

 

* Data types can be converted either implicitly or explicitly.
An implicit conversion does not use any special syntax in the source code. Implicit conversions are not visible to the user. The convert is done behind the scenes according to set of built-in rules. An explicit conversion uses a type conversion keyword and can be done by the user using CONVERT or CAST function.

 

Conclusions and Summarize

Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. Always keep and following rules, in order to make your work more compatible.

v  When you use .Net application you should use a type that is mapped correctly to the SQL Server types. Check this link for SQL Server Data Type Mappings.

v  When you specify dates in DML queries, always use constants way that are interpreted the same way for all language settings!

Ø  Use the format yyyymmdd as 20160227 (which is my next birthday, by the way).

Ø  Use explicit CONVERT statement with an explicit style parameter (both… to pass value to the database and get the value from the database).

Ø  Use escape clauses while using ADO, OLE DB, or ODBC :

§  {ts 'yyyy-mm-dd hh:mm:ss[.fff]'} such as: {ts '2016-02-27 10:02:20'}

§  {d 'yyyy-mm-dd'} such as: {d '2016-02-27'}

§  {t 'hh:mm:ss'} such as: {t '10:02:20'}

v  If the format that SQL Server use for the convert is wrong, then we might get the error “datetime data type resulted in an out-of-range value”. For example if the SQL Server tried to convert a number that more than 12 to month or number that is more than 31 to days.

v  Remember that those are only display formats. In the database the data is store in the same way, no matter what is your language!

 

So how the data is actually store in the database?
This will be my next blog :-)

  

Appendix A: Get a list of all available languages and the formats:

SELECT * FROM SYS.SYSLANGUAGES
GO

  

Appendix B: Resources and more information

I highly recommend to read more and you can start with these links

Forum questions

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d0849ea8-5a3e-4609-aa29-681cd3cd3320/problem-with-datetimepiker?forum=sqlgetstarted

Data Type Conversion (Database Engine)
https://technet.microsoft.com/en-us/library/ms191530(v=sql.120).aspx

BOL- datetime (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187819.aspx?f=255&MSPPError=-2147217396

BOL - CAST and CONVERT (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

ISO 8601
http://en.wikipedia.org/wiki/ISO_8601

SQL Server Data Type Mappings
https://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx