1

DT_DATE and DT_DBTIMESTAMP both store the year, month, date, hour, min, sec, and fractional sec.

What is the difference between DT_DATE, DT_DBTIMESTAMP?

Which of them is to be used to store the DateTime value from the SQL database?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
variable
  • 8,262
  • 9
  • 95
  • 215

2 Answers2

2

DT_DATE

The main difference between both data types is the DT_DATE is made to handle the Ole Dates used in Excel as a floating number. You can simply retrieve this number from a DT_DATE column using an SSIS expression as follows:

(DT_R8)[dateColumn]

Or even you can convert a floating column to a date using the following expression:

(DT_DATE)[OleDateColumn]

You can read more on the following answer: Is there a better way to parse [Integer].[Integer] style dates in SSIS?

DT_DBTIMESTAMP

Besides, DT_DBTIMESTAMP and DT_DBDATE are the SSIS data types used to store dates the same way that they are stored within SQL databases which is very clear from the DB characters added to the data type name.

SSIS SQL Server
DT_DBDATE Date
DT_DBTIMESTAMP DateTime

Helpful resources

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Both these seem to be similar - DT_DBTIMESTAMP and DT_DBDATE? They hold year month day hour min sec frac – variable Dec 29 '21 at 19:05
  • `Dt_dbdate` does not store the time part while the `DT_DBTIMESTAMP` does. Using `Dt_dbdate` the time part is all set to zeros. – Hadi Dec 29 '21 at 19:22
  • @variable this is explained in the following answer: https://stackoverflow.com/questions/57221410/how-to-remove-from-datetime-variable-hours-minutes-seconds-and-other-parts-in/57221736#57221736 – Hadi Dec 29 '21 at 20:04
  • @variable is there still any clarification you are looking for? – Hadi Jan 04 '22 at 20:38
1

According to Microsoft:

A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits.

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. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE.

On the other hand, DT_DBTIMESTAMP is represented by a structure that internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger limits on ranges of the dates it can present.

https://learn.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types?view=sql-server-ver15

Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • So which one to be used to store datatime value from sql database – variable Dec 28 '21 at 16:11
  • 1
    @variable, it depends on the type it has in the database. I suppose this "sql database" is SQL Server, so you may need to compare the ssis datetimes with the db datetime types here: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15 and decide the SSIS type based on the one that adjust better to the type in the DB – Jayvee Dec 28 '21 at 16:24