How Do I Extract Just The Date From A Sql Datetime Value In Ssis?

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

Solution 1:

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

Solution 2:

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.

Solution 3:

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.

Solution 4:

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

SELECTCAST( FLOOR( CAST(getdate() ASfloat) ) 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()

Solution 5:

The DATEADD() function adds a time/date interval to date and then returns the date.

Syntax is

DATEADD(interval, number, date)

Query is

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