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

sql tsql ssis business-intelligence
rrydman picture rrydman · May 9, 2009 · Viewed 35.1k times · Source

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

Michael Entin picture Michael Entin · May 11, 2009

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.

Related questions

Pivot transformation using t-sql

In SSIS there is a task called Pivot transformation that changes columns into rows, but how to do the same task in sql server using t-sql ? this is my sample table location product qty ----------------------- delhi PEPSI 100 GURGAON CAKE 200 NOIDA …

Read More
Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

I'm trying to import a flat file into an oledb target sql server database. here's the field that's giving me trouble: here are the properties of that flat file connection, specifically the field: here's the error message: [Source - 18942979103_txt [424]] …

Read More
convert Excel Date Serial Number to Regular Date

I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date Example: 36464 37104 35412 When i formatted cells in excel these are converted as 36464 => 1/11/1999 37104 => 1/08/2001 35412 => 13/12/1996 I need to do this transformation in SSIS or in …

Read More