How do I strip the date off of a datetime string in SQL SSIS?

Question

I'm working on a data warehouse project and would like to know how to (preferably in a Derived Column component in a Data flow) strip the date piece off of a SQL datetime record.

Once I have the datetime converted to just a time I am going to do a lookup on the time to find the related time record in a time dimension table.

Can someone give me a simple function to accomplish this inside a derived column transform?

Example: Transform a datetime such as "12/02/2008 11:32:21 AM" into simply "11:32:21 AM".

Solution 1

I would just do a cast to DT_DBTIME type (using Derived Column transform, or Convert type transform). DT_DBTIME contains just (hours, minutes, seconds) part of the date/time, so you'll get rid of the date part.

Solution 2

Actually if you reverse the first 2 expressions like this: (DT_DBDATE)(DT_DATE)GETDATE() instead of (DT_DATE)(DT_DBDATE)GETDATE(), then you will TRUNCATE the time off the date field.

If the DT_DATE expression is before the DT_DBDATE expression, you will still have the time portion in your output, but it will be reset to all zeroes.

Solution 3

If you need to do this in a variable expression Michael's solution won't work, but you can use the following expression:

(DT_DATE)(DT_DBDATE)GETDATE()

(DT_DBDATE) converts the current date and time to a date only. But the new datatype is not compatiple with SSIS's datetime. Therefore you'll have to use (DT_DATE) for converting to a compatible type.

Courtesy of this solution belongs to Russel Loski who has posted it in his blog: http://www.bidn.com/blogs/RussLoski/ssas/1458/converting-datetime-to-date-in-ssis

Solution 4

Ran into this with writing a report for a scheduling app, needed the time that was stored as part of a datetime data type. I formated the datetime as 0 which gives you this mon dd yyyy hh:miAM (or PM), and just did a substring of that which returned the time only in an AM/PM format.

Example below.

DECLARE @S DATETIME = GETDATE()

SELECT SUBSTRING(CONVERT(NVARCHAR(30), @S , 0) , 13 , 10) AS ApptTime
    , CONVERT(NVARCHAR(30), @S , 0) AS ApptDate

View more solutions

Comments

[adolf garlic]

This is the third answer that is giving a SQL rather than an SSIS solution!

[CJM]

Perhaps the fact that 3 people made the same mistake suggests that it was an easy mistake to make.

[rrydman]

This worked - I had to do two data conversions (one to DT_DBTIME then one to DT_STR) so it seems kind of clunky - but it works.

[Valentino Vranken]

Question is about stripping the date off (i.e. "removing"), not the time.

[Pierre-Adrien]

Doesn't answer to the original question but helped me understand how to convert my full length datetime to the appropriate format for database insertion. Thanks for that :)

[The Lyrist]

or that most people don't read the question fully and post a canned answer right away.