Take the 2-minute tour ×

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

share|improve this question
add comment

5 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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. –  rrydman Jun 10 '09 at 18:26
add comment

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.


    , CONVERT(NVARCHAR(30), @S , 0) AS ApptDate
share|improve this answer
add comment

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.

share|improve this answer
add comment

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


(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

share|improve this answer
Question is about stripping the date off (i.e. "removing"), not the time. –  Valentino Vranken Mar 23 '12 at 12:33
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 :) –  LePad Jul 25 '12 at 13:51
add comment

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

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

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

share|improve this answer
This is the third answer that is giving a SQL rather than an SSIS solution! –  adolf garlic May 14 '09 at 6:19
Perhaps the fact that 3 people made the same mistake suggests that it was an easy mistake to make. –  CJM May 14 '09 at 11:02
add comment

Your Answer


