Search code examples
timebidsssis-2008

Convert hh:mm:ss in BIDS to numeric seconds

I'm importing data from a csv using BIDS [SQL Server 2008r2]. I need to convert hh:mm:ss to numeric to be imported into SQL Server. For example

00:01:05 = 65

Current Steps:

enter image description here

The conversion looks like this. DataConversion

Solution

  • Making the assumption that you are talking about SQL Server Integration Services, you can accomplish this via a Derived Column Transformation.

    Drag a Derived Column Transformation into your data flow pipeline, and connect the source.

    enter image description here

    Open it up and add a new field, giving it a name and selecting <add as new column>. Paste the following formula into the Expression box, replacing 'SrcTime' with the name of your field: this expression will convert the time field into decimal minutes (e.g., 01:01:30 becomes 61.5)

    (DT_NUMERIC,6,2)(((DT_I4)LEFT(SrcTime,2) * 60) + 
((DT_I4)SUBSTRING(SrcTime,4,2)) + 
((DT_NUMERIC,4,2)RIGHT(SrcTime,2) / 60))

    EDIT: if you need it in seconds, not minutes (01:01:30 becomes 3690.00), you could alter the formula to be:

    (DT_NUMERIC,10,2)(((DT_I4)LEFT(SrcTime,2) * 3600) + 
((DT_I4)SUBSTRING(SrcTime,4,2) * 60 ) + 
((DT_NUMERIC,4,2)RIGHT(SrcTime,2)))

    EDIT AGAIN: Based on your version, the LEFT() function is not available in the Expression Editor. Use this formula instead:

    (DT_NUMERIC,10,2)(((DT_I4)SUBSTRING([TALK TIME],1,2) * 3600) + 
((DT_I4)SUBSTRING([TALK TIME],4,2) * 60) + 
((DT_NUMERIC,4,2)RIGHT([TALK TIME],2)))

    enter image description here This will add a new column to your pipeline, with the name you gave it in 'Derived Column Name', which will have the correct format.