views:

1499

answers:

3

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

A: 

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

Issa Qandil
When using this solution, SSIS's derived column reports that it cannot recognize the function "varchar". Any idea whats up?
Sangheili
I think this is SQL expression, it would not work inside the SSIS, of course
Michael
You can use derived column transformation in your data flow to convert the date into desired format. Following expression converts the vdate column into the format you mentioned above.
Issa Qandil
This is a SQL not an SSIS solution
adolf garlic
+3  A: 

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.

Michael
Correct http://msdn.microsoft.com/en-us/library/ms141036.aspx
adolf garlic
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.
Sangheili
A: 

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.

CJM
This is the third answer that is giving a SQL rather than an SSIS solution!
adolf garlic
Perhaps the fact that 3 people made the same mistake suggests that it was an easy mistake to make.
CJM