Read this if you are confused how to enter DATE-TIME in SQL

I was playing around DATE and TIME in SQL2012, and I was: What the heck is wrong with SQL when it comes to entering date and time values. Why they do not have clear format for doing so. Shall I enter the day then month then year or the month should be entered first?

First problem:

SQL Server does not (I repeat does NOT) provide any way to express a date and time literal. Instead, it allows you to specify a string that can be converted into a date and time data type. In other words, the only way for you to manually enter a date and time is to use character string that looks like or can be converted by SQL to date and time. Interesting!

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHEREorderdate = '20130212'; 

Now SQL server will look at the orderdate and will cast it into date and time data type. But wait, is it December or February? This is the second problem.

Second Problem:

If I enter ‘02/12/1013’, will SQL understand this as December or February?  This is where things get confusing to me first. The way SQL interrupts your character string and convert it to date and time data type is controlled by the language setting on your session.

For example, the us_english language setting sets the date format to mdy, whereas the British language setting sets it to dmy.

--This means December
SET LANGUAGE British;
SELECT CAST('02/12/2013' AS DATETIME); 
--This means February
SET LANGUAGE us_english;
SELECT CAST('02/12/2013' AS DATETIME);

By the way, you can know what language is your current session is operating in by typing

SELECT @@LANGUAGE

To be more specific, behind the scenes, the language has several settings that get set automatically when you change the language. One of those setting is the DATEFORMAT. Here is an example of using DATEFORMAT:

CREATE TABLE #TEST (MYDATE DATETIMEINSERT #TEST
VALUES ('20131202')
SET DATEFORMAT YDM
INSERT INTO #TEST
VALUES ('2013/02/12')
SET DATEFORMAT YMD
INSERT INTO #TEST
VALUES ('2013/12/02')
SELECT * FROM #TEST 
Results:
2013-12-02 00:00:00.000
2013-12-02 00:00:00.000
2013-12-02 00:00:00.000

 Note:

The LANGUAGE/DATEFORMAT setting only affects the way the values you enter are intercepted and not the format used in the output for presentation purposes. As you can see from the previous select statement output, the format that SQL is showing us the values is YYY-MM-DD hh:mm:ss.nnn.

Third Problem

Since the way SQL intercept the character string literal to date and time data type depends on the language settings in the current session, and you may have multiple sessions with multiple language sessions, the safest way is to enter date and time in a LANGUAGE-NEUTRAL-FORMAT.

This means that regardless of the current LANGAUE setting of the current session, SQL will always intercept the same way. This format for DATETIME data type is:

  • YYYYMMDD hh:mm:ss.nnn’
  • YYYYMMDDThh:mm:ss.nnn’
  • YYYYMMDD 

So, if you enter ‘2013-12-02’ , it is bullet proof that SQL will always consider this as December the second regardless of the current session’s LANGUAGE/DATEFORMAT setting.

By Ammar Hasayen Posted in SQL Tagged

Simple T-SQL Tips : Small things that matters

Hi everyone, I came across a SQL course online with so many advance topics that I found so challenging.

Nevertheless, it is the small things that any SQL developer do every day that really matters. I want to share with 3 quick killer SQL query mistakes that you can encounter every day in your work.

Tip number 1:

“Indexes do not work when data type conversion took place in your predicates”

If you have a table with column [PostalCode] that is string (i.e VARCHAR), then writing something like:

SELECT * FROM Table_Name

WHERE PostalCode = 1000

This is OMG what the heck you are doing! The type of PostalCode is VARCHAR, the type of (1000) is integer, then your predicate (PostalCode = 1000) has different data types and SQL has to do data type conversion in memory and will not use your index, in case you have index in PostalCode.

The right thing to do is:

SELECT * FROM Table_Name

WHERE PostalCode = ‘1000’

 Now both PostalCode and (1000) are strings and no type conversion is required. If you have index on PostalCode, it will be used.

Tip number 2:

“Your predicate or filter in WHERE statement, should not touch the column name side of the filter in order to use indexes”

The bad way:

SELECT * FROM Table_Name

WHERE RIGHT(FirstName,3) = ‘mr.’

The right way:

SELECT * FROM Table_Name

WHERE FirstName LIKE ‘mr.%’

FirstName is the column name, and you should not touch it, manipulate it or use scalar functions on it, or else no index will be applied. In other words, a table scan will happen which is so long and expensive.

So always avoid doing manipulation on the FirstName because this is your column infromation. Do not use UPPER(), Substring() or any other manipulation on it. Instead, do your manipulation on the other side of the statement.

Sometime you want to check if the year of birth is 2013 for example, so instead of writing WHERE YEAR(HireDate) = 2013, write something like WHERE HireDate >= 1/1/2013 AND HireDate < 1/1/2014

Tip number 3:

Use COALESCE() instead of  ISNULL().

COALESCE is a standard SQL  function while ISNULL() is a T-SQL function, so COALESCE is more standardized way to do stuff.

They both do the same stuff, if the first argument is NULL, then return the second argument. One different though is that COALESCE will do type and size conversion while ISNULL() will not.

Example:

 You have a table with title column that stores (Mr.) and (Miss.) for each person, and this column accepts NULLs. You designed the column to be CHAR(5). Now you want to view the content of this column, and you will use ISNULL to replace NULL values with ‘unknown’. So you would say:

SELECT ISNULL(Title,’Unknown’)

FROM TableName

The problem and surprise here is that ISNULL() will always return the size and type of the first argument which is CHAR(5), so if there is a NULL, it will return ‘Unkow’  instead of ‘Unkown’ .

Instead, you can say:

SELECT COALESCE(Title,’Unknown’)

FROM TableName

This was a quick post that I wanted to share because while the Database developers puts the right indexes, the developers sometimes screw things without knowing that.

By Ammar Hasayen Posted in SQL Tagged