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?
CodePudding user response：
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.
CodePudding user response：
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 |