Don’t cutoff yourself with dates in T-SQL – Did you know…

Almost all the applications we use have a lot of options. And, sometimes we even stumble across them by accident.
Who’s ever heard something like “Normal user don’t even use 5% of Excel capabilities!”?

Other options, we know they exist but because the default value it’s ok (so far) we tend to forget it. It is just sitting there, waiting for the day we want/need to change it.

The oddity

Few days ago I was talking with a friend that show me some “odd behavior” when working with dates on SQL Server.

Are you mad?! Is this SQL Server bugged?

In case you ask, this is my table definition:

CREATE TABLE DatesFormat
(
    dt DATETIMEOFFSET DEFAULT ('1900-01-01')
);
GO

Let’s take a closer look at the SELECT.

  1. On the 1st column, we can see the complete value of the column
  2. The 2nd column picks the year from the date column
  3. The 3rd one also picks the year from a datetime but declared as string.

But why don’t the 2nd and 3rd columns return the exact same value?!

What is your interpretation?

What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the DATEPART function is returning the wrong value?

Both are returning the correct value! Say hello to “Two Digit Year Cutoff” configuration

You can find it on the advanced tab in the Server Proprieties:

Or by running the sp_configure command:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'two digit year cutoff';  
GO 

Or even using dbatools PowerShell module:

Get-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff'

Output:

That’s right! This option is the one that makes the last column value being translated to 2000 and not 1900.

If we change the configuration to 1999 instead of 2049 (default value) the result of the DATEPART will be 1900 but having it as 2049 will convert the year as 2000 (from the date that is a string).

The test:

-- Check the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- Notice the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat
GO

-- Change the configuration
EXEC sp_configure 'two digit year cutoff', 1999;  
GO  
RECONFIGURE ;  
GO 

-- Confirm the running value
EXEC sp_configure 'two digit year cutoff';
GO

-- See the different value on the 3rd column
SELECT dt, DATEPART(YEAR, dt), DATEPART(YEAR, '01-Jan-00 12:00:00 AM +00:00') 
  FROM dbo.DatesFormat

Output:

Remember, this only happens when you use a literal string.

To set a new value using dbatools:

Set-DbaSpConfigure -SqlInstance sql2016 -ConfigName 'TwoDigitYearCutoff' -Value 1999

Output:

What about the returning value?

Yeah, I know, why is the value of the first column returned on that format? You are used to seeing in the format of yyyy-MM-dd right?
I’ll explain this in a next post! Stay tuned.

Summary

Next time you have to work with dates in formats like dd-MMM-yy remember that “Two Digit Year Cutoff” configuration exists and may mislead you.

To complete the question…”Did you know that ‘Two Digit Year Cutoff’ configuration can trick you?” Now you do.

Thanks for reading.

Did you know…you can change the default file encoding for new query on SSMS?

If you have been reading my last blog posts, you know that I’m currently working on a SQL code migration from Firebird to SQL Server.

The client provided the scripts with all modules (Stored Procedures, functions, etc) and the steps I’m following (roughly speaking) for converting are:

  1. Open new query window
  2. Copy and paste de object code
  3. Save the file
    This is how the file look like:

  4. Run a PowerShell script that does a find and replace based on a hashtable. Apply all the changes and save the file again.

  5. The file refresh on SSMS
    This is how the file look like after the find and replace:

  6. Unicode characters are broken 😦

So…what is happening?

The file that is used to create a new query window has ANSI encoding but when I save the file on the PowerShell script I save it as UTF-8 because the client have comments on the code with unicode characters.

On this process, the unicode characters are replaced by some symbols.

How to solve?

We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.

This way, when use the keyboard shortcut CTRL + N to open a new window and hit Save, I’m saving as UTF-8 which means that the PowerShell script will do the find and replace, save the file and preserve the unicode characters. 🙂

Where is that default file?

My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).
Inside this folder we can find the file SQLFile.sql.

We just need to open it, for example with notepad, do the Save As and choose the encoding we want.

Now if you go to the SSMS, open a new query window and hit save, you can confirm that the file is saved by default with the new encoding you have setup.

With this I have eliminated one tedious step that forced me to do the Save As and choose the UTF-8 every single file I wanted to save. I have hundreds of objects so, this small improvement times hundreds of iterations save me a good amount of time!

Thanks for reading.

Did you know…you can’t do arithmetic operations with datetime2 like with datetime?

I’m currently working on a SQL code migration from Firebird to SQL Server and I hit an error that I haven’t seen for some time.

The error message is the following:

Msg 206, Level 16, State 2, Line 4
Operand type clash: datetime2 is incompatible with int

This ringed the bell right away! Somewhere on the code someone was trying to do an arithmetic calculation without using the proper function.

How so?

In the early days of my T-SQL coding, I used to do this a lot. Also, I still see some code from other applications that still use it this way. Take, for instance, the following code that returns all orders placed with more than 1 day old:

SELECT OrderId, ClientId, Quantity, OrderDate
FROM dbo.Orders
WHERE OrderDate < GETDATE() -1

For this example let’s say that the OrderDate column is a DATETIME2. This works just fine because the GETDATE() function returns a DATETIME value and thus we can subtract one day from it.

If we define a variable of DATETIME2 datatype and assign it a GETDATE() value, then attempt to subtract-1 from the variable, an error will yield!

DECLARE @vOrderDate DATETIME2 = GETDATE()
SELECT OrderId, ClientId, Quantity, OrderDate
FROM dbo.Orders
WHERE OrderDate < @vOrderDate - 1

Msg 206, Level 16, State 2, Line 20
Operand type clash: datetime2 is incompatible with int

But it was working!?

Yes it was on the source engine (Firebird) and it will still work on the destination (SQLServer) if the datatype is still the same – DATETIME.

What happened here was the column datatype was changed during the schema migration from DATETIME to DATETIME2.

NOTE: The most recent date/time datatypes appeared with SQL Server 2008. They are DATE, TIME, DATETIME2, DATETIMEOFFSET.
Also, bear in mind that actually the DATETIME and SMALLDATETIME datatypes are the only from the date/time family that supports this arithmetic operations.

How to fix this error?

To solve this, we need to convert the

@vOrderDate  -1

to

DATEADD(dd, -1, @vOrderDate) 

Whole code looks like:

DECLARE @vOrderDate DATETIME2 = GETDATE()
SELECT OrderId, ClientId, Quantity, OrderDate
FROM dbo.Orders
WHERE OrderDate < DATEADD(dd, -1, @vOrderDate) 

this way, it will work with DATETIME, DATETIME2, DATE, SMALLDATETIME and DATETIMEOFFSET datatypes.

NOTE: DATEADD also support TIME datatype, I didn’t mention because on our example we are subtracting DAYS, and as (at least I) expected this will give an error.

Wrap up

Are you thinking about changing your DATETIME columns to DATETIME2? Or are you just beginning to use it in your projects?
Documentation encourages you to do so (https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) but as you could see from this post, you need to pay attention and do the proper testing and T-SQL code revision.

Thanks for reading.