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".

Answer 1

I guess it will be something like this

SELECT convert(DATETIME,convert(VARCHAR, getdate(),114))

You may replace getdate() with your Datetime Field

For more information please check this link

Answer 2

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.

Answer 3

I personally use a series of functions for this. E.g.:

ALTER FUNCTION [dbo].[TIMEVALUE]

( @Datetime datetime ) RETURNS datetime AS BEGIN RETURN (@Datetime - CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime)) END

I'd love to claim all the credit but it should really go to this guy.