(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 :-)