One of the things that I was frequently told during my schooling was to initialize the variables that I use in my programs and scripts. The obvious benefit of having such mentors is that it has now become a matter of habit for me to initialize every variable in my SQL scripts.

One script required me to use the DATETIME datatype and I added an initialization to the variable explicitly. The script was simple enough and I had it all setup as pseudocode on my little notepad. However, in translating my thoughts from the notepad to the SQL script, I made a fatal error – I ended up initializing the DATETIME variable with a string. What follows is an account of the rather painful realization about the impact of my error.

Normally, when we attempt to assign a string value to a DATETIME variable, it results into an error.

DECLARE @myDate DATETIME;
SET @myDate = 'abcd';

SELECT @myDate;
GO

Immediately, we receive the following error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

However, supplying valid dates as string does work.

--Passing valid dates as strings does work
DECLARE @myDate1 DATETIME, 
        @myDate2 DATETIME;
SET @myDate1 = '08/25/2014'
SET @myDate2 = 'August 25, 2014';

SELECT @myDate1 AS MyDate1, 
       @myDate2 AS MyDate2;
GO

/* RESULTS:
MyDate1                 MyDate2
----------------------- -----------------------
2014-08-25 00:00:00.000 2014-08-25 00:00:00.000
*/

However, I had initialized the variable with an empty, i.e. blank string. This does not return any error. However, it does do something different – something that might cause you to go around in circles unless maybe the code is reviewed by another set of eyes (which is what happened in my case) or after you take a much deserved break.

This is what I had done:

DECLARE @myDate DATETIME;
SET @myDate = '';

And the result was something that threw me off because all subsequent calculations that I had done on that particular date variable were returning incorrect results. After a couple of minutes and some help in reviewing the code again, I realized that:

Initializing a DATETIME, DATE or TIME variable with empty string sets it to 01/01/1900 (default value for DATE & TIME data-types).

Initialization with an empty string had initialized my date variable with the value – 00hrs on 01/01/1900, which is the default value for DATE & TIME data-types.

DECLARE @myDate DATETIME;
SET @myDate = '';

SELECT @myDate AS MyDate;
GO

/* RESULT
MyDate
-----------------------
1900-01-01 00:00:00.000
*/

NOTE: Attempting to use an empty string as default value for DATE and TIME data-types in the ISNULL() and COALESCE() functions also results into 01/01/1900 being returned as the default value.

Moral of the story

• Always initialize all variables – it serves a dual purpose by both preventing and (as in this case) identifying potential errors
• Be careful about the value being used for the initialization – it is possible that one may end up with an undesired effect of an incorrect initialization
• It’s always good to have code reviews!

Further Reading

• DATE and TIME data-types [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.