How do I extract just the date from a SQL datetime value in SSIS?

Question

How do I convert a datetime to just a date in SSIS?

I'm trying to perform an aggregate query which will group by a particular date. In order to do that, I'm trying to extract just the date portion of the timestamp(stored as a datetime) so I can perform the GROUP BY on the next step.

I've looked at the DATEPART function, but it looks like I would have to call it multiple times to retrieve the month, date and year, concatenate them together and then parse the new date. There has got to be a better way to do this.

Answer 1

Unfortunately, that is the best way to do it (in my opinion) and depending on your destination column (if that is where the flow is ending) it will still have the 00:00:00:000 on it. But you probably already figured that =P.

Answer 2

Here's you you can do it in T-SQL, I'm assuming SSIS allows full access to SQL Server functions:

 SELECT CAST( FLOOR( CAST(getdate() AS float) ) AS datetime)

Basically, it converts the date to a float (time is a fraction of a day), uses floor to chop off the decimal (cast to int might round up), then casts the float back to a date.

Efficient, works, and doesn't require multiple calls to DATEPART()

Answer 3

select DATEADD(dd,0, DATEDIFF(dd, 0, GETDATE()))

Answer 4

My first question would be if you are dealing with data contained in dbs on the same server-why not just perform all of your casting and grouping in SQL Server before you bring the data over to SSIS? For performance reasons, it is best to do any massaging of the data SQL Server before bringing it into SSIS. However, if you are dealing with heterogeneous data (oracle, excel, text files, data from a different sql server instance), then you could use the derived column transformation and convert the date using the cast (DT_DBDATE) then use the aggregate component to do your group by.

Answer 5

In SSIS you can use Data Conversion Transformation, for input value you have DT_DBTIMESTAMP and for output use DT_DBDATE