SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST

Note: I have used SQL Server 2012 for this small fun experiment.

Here is what we are going to do. We will run the script one at time instead of running them all together and try to guess the answer. I am confident that many will get it correct but if you do not get correct, you learn something new.

Let us create database and sample table.

CREATE DATABASE DB2012
GO
USE DB2012
GO
CREATE TABLE TableDT
(DT1 VARCHAR(100), DT2 DATETIME2,
DT1C AS DT1, DT2C AS DT2);
INSERT INTO TableDT (DT1, DT2)
SELECT GETDATE(), GETDATE()
GO

There are four columns in the table. The first column DT1 is regular VARCHAR and second DT2 is DATETIME2. Both of the column are been populated with the same data as I have used the function GETDATE(). Now let us do the SELECT statement and get the result from both the columns.

Before running the query please guess the answer and write it down on the paper or notepad.

Question 1: Guess the resultset

SELECT DT1, DT2
FROM TableDT
GO

Now once again run the select statement on the same table but this time retrieve the computed columns only. Once again I suggest you write down the result on the notepad.

Question 2: Guess the resultset

SELECT DT1C, DT2C
FROM TableDT
GO

Now here is the best part. Let us use the CAST function over the computed columns. Here I do want you to stop and guess the answer for sure. If you have not done it so far, stop do it, believe me you will like it.

Question 3: Guess the resultset

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO

Now let us inspect all the answers together and see how many of you got it correct.

Answer 1:

Answer 2:

Answer 3: 

If you have not tried to run the script so far, you can execute all the three of the above script together over here and see the result together.

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO

Here is the Saturday Fun question to you – why do we get same result from both of the expressions in Question 3, where as in question 2 both the expression have different answer. I will publish the valid answer with explanation in future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST

  1. As Q1 & Q2′s of field data types are same that’s the reason result set is same.
    In Q3 then DT1C field’s data type is nvarchar and type casting to datetime2 type that’s the reason it is converting like this

  2. Varchar may not provide values in seconds, but datetime2 specifically provides values in seconds and milliseconds.

    Even after converting Varchar into Datetime2, it will not provide the same value as of datetime2.

  3. It isn’t a direct issue of varchar. I think SQL performis an internal convert(varchar(100),getdate). Since a style was not specified it is applying the default (100) which is Mon dd yyyy hh:mmAM. This excludes seconds and milliseconds in the text string. If we used convert(varchar(100),getdate(),113) our answers would be the same. Unfortunately Cast is ANSI 92 standard while Convert is not.

  4. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s