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 (https://blog.sqlauthority.com)