Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: What's the difference between DT_DATE and DT_DBTIMESTAMP

Database people don't generally like date values too much. They're too fiddly. There's god-knows-how-many ways of representing them...and don't even get me started on regional date settings!!

 

Naturally SSIS contains a datatype that can hold dates. In fact it has a few, and again its important to understand the nuances of using them. Variables can be of type datetime and columns in the pipeline can be of type DT_DATE, DT_DBTIMESTAMP or DT_DBDATE. That's 4 different methods of holding dates. Which one should you use? BOL is not much help here unfortunately so I decided to investigate it for myself.

The first test I ran was to insert identical datetime values into three columns in the pipeline using a script source component; one of type DT_TIME, one of DT_DBTIMESTAMP and one of DT_DBDATE. Here is a screenshot showing the configuration of the output and following that the code to populate the pipeline with a row.

20051115outputconfig.JPG

20051115scriptsource.JPG

I viewed the results using SSIS's handy Data Viewer feature. Here's what you see:

20051115dataviewer2.JPG

Straightaway its obvious that DT_DBDATE merely strips off the time precision of the datetime value. That could turn out to very handy.

DT_DATE and DT_DBTIMESTAMP are a bit of a mystery however. They look exactly the same to me. Here's what BOL had to say on the matter:

DT_DATE

A date structure that consists of year, month, day, and hour.

DT_DBDATE

A date structure that consists of year, month, and day.

DT_DBTIMESTAMP

A timestamp structure that consists of year, month, hour, minute, second, and fraction.

Well its correct about DT_DBDATE. I'll concede that the DT_DBTIMESTAMP value does indeed have a fraction in there but the data viewer just isn't showing it - so BOL is correct there too. However, does it look to you as if DT_DATE only consists of year, month, day and hour? Not to me it doesn't.

I enquired of Microsoft what was going on and luckily the query went to Marianne Willumsen who provided the following, very detailed, explanation.

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. This means that some times DT_DATE can represent minutes, seconds, and even milliseconds as well. However, floating point value cannot represent all real values and there are therefore limits on the range of dates that can be presented in DT_DATE.

On the other hand, DT_DBTIMESTAMP is represented by a structure with individual fields for each of year, month, day, hours, minutes, seconds, and milliseconds an has no limits on ranges of the dates it can present .

So there you have it. It seems that the biggest single difference between DT_DATE and DT_DBTIMESTAMP is that their internal representation is completely different.

But which one should you use in your packages? At the present time I don't know the answer to this question but I'd hazard a guess that the number representation of a DT_DATE means that it can be processed much quicker than a DT_DBTIMESTAMP however if you require minute and/or second precision in your dates then you have no choice but to go for DT_DBTIMESTAMP.

Marianne also told me that the section of BOL that I have copied above had been changed and will be available in the December 2005 release of BOL.

If you have experience of using the datatypes then please let me know - I'm all ears. I'd be keen to know if DT_DATE really can perform significantly quicker than DT_DBTIMESTAMP in large datasets.

-Jamie

 

 

Published Tuesday, November 15, 2005 7:51 PM by jamie.thomson

Comments

 

Gary Mason said:

Jamie,

don't know if you answer questions here, but I have a conversion issue: I'm trying to populate a table from an Excel sheet. The Excel sheet has datetime columns that the dataflow source has typed as DT_DATE. The database table has a column of type dt_dbtimestamp. When I try to execute the data flow, I get an error:

Error at Data Flow Task [SQL Server Destination [87]]: The column "Start Time" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.

December 6, 2005 4:24 PM
 

Dar said:

Can you please tell me how can I convert 8 character string into SQL server DatTime datatype. I used Date conversion string in SQL server 2000.
February 3, 2006 5:24 PM
 

MaryAnn said:

The same problem as Gary Mason's.  my package fails because of the conversion between type DT_DATE and DT_DBTIMESTAMP is not supported.  
Do you have idea on how to go around this problem?
April 14, 2006 10:36 PM
 

jamie.thomson said:

MaryAnn/Gary,
I am guessing that you are trying to do an implicit conversion from DT_DATE to DT_DBTIMESTAMP. Use the Data Conversion component or Derived Column Component to do an explicit conversion.

-Jamie
April 18, 2006 1:13 PM
 

Anthony Desa said:

I have tried what Jamie.thomson suggested. I tried both, Data Conversion componenet as well as Derived Column but the error output remains same...

Did any one had any luck in soving this mystory....

August 23, 2007 6:14 PM
 

Anthony Desa said:

I got this solution from one of the web site and it works great for me....

I got the way to do this,

I followed the following steps

between the source and destination I've added a data conversion .

In the data conversion I have converted the data for the "Date of Update" column from DT_Date to DT_DBDatetime and created a column "Date Of Update 1".

While mapping the columns I mapped the source column "Date Of Update 1" with destination "DateOfUpdate"

Similarly I was getting the errors for DT_NTEXT, and DT_WSTR, I applied the same procedure there.

August 23, 2007 6:25 PM
 

Mark said:

The Data Conversion SSIS Data Flow Item in VS 2005 does not contain an option for converting to DT_DBDatetime. Am I missing something? I have the same problem converting dates.

January 24, 2008 11:43 PM
 

jamie.thomson said:

Mark,

Sorry, I'm not sure which data type you mean. Do you mean DT_DBTIMESTAMP?

-Jamie

January 25, 2008 4:33 PM
 

Tom said:

Anthony's suggestion worked like a charm.  In the data conversion editor, change the datatype of you date column to database timestamp[dt_dbtimestamp].  dt_dbdate and dt_dbtime will not work.  So apparently, all the date datatype in sql server 2005 is a timestamp type; smalldatetime, datetime, timestamp?  Can anyone confirm this?  Why is there a timestamp besides datetime.  MS makes it so confusing for everyone.

Thanks a bunch,

May 29, 2008 6:16 PM
 

Shirley said:

I am reading in a flat file that has a string date in the format "01/01/2008".  I need to convert the date to a datetime to insert into a SQL database.  The problem I am encountering is that the date field is somethims blank (I assume spaces).  I have not been able to find a way to convert the date to null if the data is spaces.  Any suggestions?

July 25, 2008 5:48 PM
 

shirley said:

Also, is there a way to check if the data in the flat file is a valid date?

July 25, 2008 6:14 PM
 

jamie.thomson said:

Shirley,

"Any suggestions?"

You'll need a conditional operator in yuor Derived COlumn component. Use it to check if the incoing value is in the required format and if it is process it as normal and if it isn't, nsert a NULL value [i.e. NULL(DT_DBTIMESTAMP) ]

"Also, is there a way to check if the data in the flat file is a valid date?"

There is no function to do this unfortuantely. You'll need to use string manipulation to test it or resort to using the script component.

-Jamie

July 28, 2008 10:54 AM
 

a_n00b_us said:

The conditional operator will work well for you, but not if you have say more than 10 cols that could possibly have a blank string as a date. That would mean you would need to nest multiple conditional splits which is not a good solution. Instead dont use conditional split and try using a derived column and use a conditional operator such as this TRIM(["col_with_possible_blank_date"]) == "" ? (DT_STR,12,1252)NULL(DT_STR,255,1252) : TRIM(["col_with_possible_blank_date"]) as the expression

I had your exact problem, but my data had multiple cols with blank string where dates were supposed to be. Started out with a conditional split then realised that what if 1 of the cols has a vaule but another has a blank string - I would need to create a seperate conditional split for each case...

August 27, 2008 5:22 AM
 

a_n00b_us said:

following my last commnet - I forgot to add, you can then cast a null string to a null date type using a Data Conversion task.... Which was what I originally wanted, but couldn't do because the field had a blank string - now it has a proper null value which can be converted between types.

Cheers.

August 27, 2008 5:25 AM
 

Padmanabh said:

I had the same problem with DT_Date to DT_DBDatetime Conversion.

I was lucky that I visited this block.

I read Anthony Desa's Comment on the problem

That did the trick for me.

I want to thank Anthony Desa for the help.

September 11, 2008 9:23 AM
 

shiv said:

i have 2 problem regarding string to database timestamp conversion.

1.in excel sheet where my data is ,date column is in string format(no changes in data type manually) when i m using this data for execution nulls r coming or data like 27384 in that column is coming when i set IMEX=1 in extended properties.

what i should do i dnt know.i tried with all sort of possibilities b4 n after reading these blog contents bt still hanging!!

2.my string consist of only date so if i want to use derive colume trnx for substring n concatenation what i hav to take in substring of time when time is not coming in string of data?

thnx

September 13, 2008 9:34 PM
 

anu said:

to jamie n the rest of the gang here,

i have issues wt derived column when joining a date field with a time field (mysql) to 1 datetime field(mssql)

my current expression is

(DT_DATE)((DT_STR,20,1252)thedate + " " + (DT_STR,20,1252)thetime)

and output field datatype is ..database timestamp [DT_DBTIMESTAMP]

but wat i get is only the date part the time it dosent convert it gives me 00:00:00

any help is apprerciated

November 12, 2008 5:21 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems