Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Understanding Datetime column - Part I

May 25 2010 12:00AM by Madhivanan   

There are N number of questions asked in the forums about handling dates in query
Most of the people who ask questions dont understand how datetime column works in SQL Server
Some of the questions frequently asked are about

1 using dates in the WHERE clause
2 formatting dates using SQL
3 inserting dates to the table with specific date format
etc

In this series of blog posts, I would explain them with examples
These example are for versions prior to 2008

Internal Storage

Many people think that dates are actually stored with specific formats like MM/DD/YYY, DD/MM/YYYY, etc in the table.
Some may think they are stored in YYYY-MM-DD HH:MM:SS format becuase when they select date columns Query analyser display them in such a format

But SQL Server stores datetime values as a two 4-byte integers
First 4-byte for Date value (number of days from base date 1900-01-01)
Second 4-bytes for time value (number of milliseconds after midnight)

Let us see an example

declare @mydate datetime
set @mydate='2009-12-10 18:32:55:873'
select 
	@mydate as source_date,
	datediff(day, '1900-01-01',@mydate) as no_of_days,
	convert(char(15),@mydate,114) as time_part, 
	datediff(millisecond, '1900-01-01',convert(char(15),@mydate,114)) as number_of_milliseconds

The result is

source_date             no_of_days  time_part       number_of_milliseconds
----------------------- ----------- --------------- ----------------------
2009-12-10 18:32:55.873 40155       18:32:55:873    66775873

From the above example it is very clear that datetime values are converted to integers 40155 and 66775873
and stored in the column Now we will see how these integers are converted to datetime

select 
	dateadd(day,40155, '1900-01-01') as date,
	dateadd(millisecond,66775873,dateadd(day,40155, '1900-01-01')) as date_time

The result is

date                    date_time
----------------------- -----------------------
2009-12-10 00:00:00.000 2009-12-10 18:32:55.873

When you want to retreive dates, SQL Server does a convertion and give date with time value as shown in the above example

Date Ranges

Datetime
Minimum value : January 1, 1753
Maximum value :December 31, 9999

Smalldatetime
Minimum value : January 1, 1900
Maximum value :June 6, 2079

As you see above, any value that is out of that range will lead to error

Rounding

Datetime

Datetime value is rounded to 0.000,0.003 or 0.007 milliseconds

select cast('2010-01-01 12:45:34.755' as datetime)

Result is

dates
-----------------------
2010-01-01 12:45:34.757

Note the millisecond part it is 757 and not 755 as in the original time
We will see how based on the last digit of the millisecond values are rounded

Last Digit	   Rounded value
----------     -----------------
0 or 1			0                         
2,3 or 4		3
5,6,7 or 8		7
9               0 ( increase previous digit) 

Now run the following code to understand how millisecond roundings happen

select '2010-01-01 12:45:34.750' as original_milliseconds, cast('2010-01-01 12:45:34.750' as datetime) as rounded_milliseconds
union all
select '2010-01-01 12:45:34.751' , cast('2010-01-01 12:45:34.751' as datetime) 
union all
select '2010-01-01 12:45:34.752' , cast('2010-01-01 12:45:34.752' as datetime) 
union all
select '2010-01-01 12:45:34.753' , cast('2010-01-01 12:45:34.753' as datetime) 
union all
select '2010-01-01 12:45:34.754' , cast('2010-01-01 12:45:34.754' as datetime) 
union all
select '2010-01-01 12:45:34.755' , cast('2010-01-01 12:45:34.755' as datetime) 
union all
select '2010-01-01 12:45:34.756' , cast('2010-01-01 12:45:34.756' as datetime) 
union all
select '2010-01-01 12:45:34.757' , cast('2010-01-01 12:45:34.757' as datetime) 
union all
select '2010-01-01 12:45:34.758' , cast('2010-01-01 12:45:34.758' as datetime) 
union all
select '2010-01-01 12:45:34.759' , cast('2010-01-01 12:45:34.759' as datetime) 

The result is

original_milliseconds   rounded_milliseconds
----------------------- -----------------------
2010-01-01 12:45:34.750 2010-01-01 12:45:34.750
2010-01-01 12:45:34.751 2010-01-01 12:45:34.750
2010-01-01 12:45:34.752 2010-01-01 12:45:34.753
2010-01-01 12:45:34.753 2010-01-01 12:45:34.753
2010-01-01 12:45:34.754 2010-01-01 12:45:34.753
2010-01-01 12:45:34.755 2010-01-01 12:45:34.757
2010-01-01 12:45:34.756 2010-01-01 12:45:34.757
2010-01-01 12:45:34.757 2010-01-01 12:45:34.757
2010-01-01 12:45:34.758 2010-01-01 12:45:34.757
2010-01-01 12:45:34.759 2010-01-01 12:45:34.760

So the last digit of the millisecond is always 0,3 or 7

SmallDatetime

SmallDatetime value is rounded to 1 minute Values of 29.998 seconds or less are rounded down to the nearest minute; values of 29.999 seconds or more are rounded up to the nearest minute.

The following example explains it

select '2010-01-01 12:45:24.755' as original_smalldatetime,	cast('2010-01-01 12:45:24.755' as smalldatetime) as rounded_smalldatetime
union all
select '2010-01-01 12:45:34.755' as original_smalldatetime,	cast('2010-01-01 12:45:34.755' as smalldatetime) as rounded_smalldatetime

The result is

original_smalldatetime  rounded_smalldatetime
----------------------- -----------------------
2010-01-01 12:45:24.755 2010-01-01 12:45:00
2010-01-01 12:45:34.755 2010-01-01 12:46:00

Millisecond expression

There is a difference in expressing the millisecond if it is less than 3 digits Let us take this example

select 
        cast('2010-01-01 12:45:34.79' as datetime) as date1, 
        cast('2010-01-01 12:45:34:79' as datetime) as date2

The result is

date1                   date2
----------------------- -----------------------
2010-01-01 12:45:34.790 2010-01-01 12:45:34.080

As you see there is a different millisecond value in the result because

when a dot is used,
1 A single digit denotes tenths-of-a-second
2 Two digits denote hundredths-of-a-second
3 Three digits denote thousandsths-of-a-second

when a colon is used, the number denotes thousandsths-of-a-second

So, in short, if the number of milliseconds is less than 3, Zeroes are added on the right side if a dot is used and Zeroes are added on the left side if a colon is used in order to make it three digits.
So you should be careful when expressing millisecond values

Keep following this series of posts.
Refer Part II that gives more informations about date formats
Refer Part III that gives more informations on how to use date values in the WHERE clasue
Refer Part IV that gives more informations on the best practises when using date values 

Tags: t-sql, sql_server, datetime, BRH, datatype,


Madhivanan
3 · 40% · 12862
1 Readers Learned from this post
Guru Samy Learned from this post on 3/13/2012 3:38:00 AM
Profile · Blog
2
 
0
Lifesaver
 
0
Refreshed
 
1
Learned
 
0
Incorrect



Submit

3  Comments  

  • very good and informative article.

    commented on May 29 2010 8:22AM
    Pinal Dave
    148 · 1% · 326
  • Thanks Pinal

    commented on May 31 2010 3:24AM
    Madhivanan
    3 · 40% · 12862
  • Good Article Madhivanan

    commented on Jul 12 2013 8:48AM
    biradar26
    3015 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Understanding Datetime column - Part I" rated 5 out of 5 by 2 readers
Understanding Datetime column - Part I , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]