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)
7 Comments. Leave new
Q1 and Q2 have same answer becaz. they have same field type.
Due to Cast Function Varchar is converted to DateTime Thats why we are seeing the difference.
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
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.
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.