Post reply

Expression for converting a string variable represnting time to minutes

  • June 24, 2010 at 4:39 am

    #157034

    Hi,

    I am trying to convert a varchar field, in the form of hh:mm, to an int by counting the minutes.

    Here are examples of my source field and it's matching calculated target field:

    source target

    --------------------------------

    01:00 60

    00:45 45

    This is the expression I'm using:

    (SUBSTRING(Source,1,1) == "0" ? (SUBSTRING(Source,2,1) == "0" ? (DT_I4)"0" : (DT_I4)SUBSTRING(Source,2,1)) : (DT_I4)SUBSTRING(Source,1,2)) * 60 + (SUBSTRING(Source,3,1) == "0" ? (SUBSTRING(Source,4,1) == "0" ? (DT_I4)"0" : (DT_I4)SUBSTRING(Source,4,1)) : (DT_I4)SUBSTRING(Source,3,2))

    A little explanation: I'm checking whether the substring has an initial 0 char, or two 00, and calculating accordingly.

    The expression is valid by the Derived Column Transformation Editor, but somehow fails the ETL.

    Please have a look and let me know what's wrong with my code, I guess I am missing something out, and the error message I received doesn't provide a lot of info about it.

    Thanks,

    Arik

  • June 24, 2010 at 5:00 am

    #1184421

    What exactly is going wrong? Does the package crash or do you just receive faulty results?

    Can you give the error messages?

    Are there any null values in the source?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 24, 2010 at 5:02 am

    #1184422

    Have you tried using datetime functions for this?

    SELECT DATEDIFF(mi, '1900-01-01 00:00', '1900-01-01 ' + '00:45')

    SELECT DATEDIFF(mi, '1900-01-01 00:00', '1900-01-01 ' + '01:00')

    SELECT DATEDIFF(mi, '1900-01-01 00:00', '1900-01-01 ' + '01:01')

    SELECT DATEDIFF(mi, '1900-01-01 00:00', '1900-01-01 ' + '01:59')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 24, 2010 at 6:15 am

    #1184451

    Thanks Chris, it sure looks much more simple that way.

    But yet it still doesn't work. When I copied this expression to the expression column in the SSIS edit box, it rejected the syntax with an error, saying something about input column name mi cannot be found.

  • June 24, 2010 at 6:18 am

    #1184454

    arik_beri (6/24/2010)

    Thanks Chris, it sure looks much more simple that way.

    But yet it still doesn't work. When I copied this expression to the expression column in the SSIS edit box, it rejected the syntax with an error, saying something about input column name mi cannot be found.

    Chris used the T-SQL syntax. In a derived column, you should use "mi". (with the quotes)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 24, 2010 at 6:27 am

    #1184456

    da-zero (6/24/2010)

    arik_beri (6/24/2010)

    Thanks Chris, it sure looks much more simple that way.

    But yet it still doesn't work. When I copied this expression to the expression column in the SSIS edit box, it rejected the syntax with an error, saying something about input column name mi cannot be found.

    Chris used the T-SQL syntax. In a derived column, you should use "mi". (with the quotes)

    Thanks - I missed that 🙂

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 24, 2010 at 6:34 am

    #1184458

    Thanks a lot guys, it works like a charm 😀

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply