Wednesday, September 21, 2016


As a data integration specialist, i have been writing sql scripts to fetch data from one database and load those records in another database; ofcourse after some manipulation. 
Ah! when it involves datetime stored in a string format in the source table then it can be problematic for you to manipulate it correctly.
I am showing a sample (ofcourse just the datetime stuff) here to understand better:-

Sample

create table SourceData( offered_date nvarchar(30) )

insert into SourceData values('2016-09-20 15:59:29')
insert into SourceData values('2016-09-20 15:59:40')
insert into SourceData values('2016-09-20 15:59:30')

select *from SourceData
Here is how the output look like:- 

offered_date
2016-09-20 15:59:29
2016-09-20 15:59:40
2016-09-20 15:59:30

Scenario
My task was to get this data aggregated on an hourly basis. So i simply converted the string value in a datetime and got the hour value.
precisely as follows:-

select datepart(hour, convert(smalldatetime,offered_date)) Offered_Hour,*
from SourceData

Offered_Hour    offered_date
15    2016-09-20 15:59:29
16    2016-09-20 15:59:40
16    2016-09-20 15:59:30


Issue
Here is the twist! first record is correct but the next two records are wrong! 
so the conversion function to smalldatetime has rounded the seconds value to the minute and this has caused 15:59:30 to appear as 16:00:00
SmallDatetime gives precision to a minute level only (rounding off seconds value)  

Solution
so i ended up using datetime2 datatype instead of smalldatetime that gives you the exact value after conversion. 

Here is how it looks like now:-

select datepart(hour, convert(datetime2,offered_date)) Offered_Hour,*
from SourceData


Offered_Hour    offered_date
15    2016-09-20 15:59:29
15    2016-09-20 15:59:40
15    2016-09-20 15:59:30


Yes Yes now it gives the correct hour value!! 
Starting with Sql server 2008 database developers are getting familiar with the datetime2.
Clearly, datetime2 is an upgrade of smalldatetime when it comes to precision. 

No comments:

Post a Comment