Post reply

Type Cast error while converting time into seconds

  • September 24, 2010 at 1:15 pm

    #224310

    Hi Guys,

    I want to convert time part into Seconds as below in derived column transformation-

    0:01:04.50 = 64.50

    h:mm:SS

    When I do this operation upto seconds part, it works fine. Only error comes when it tries to include fraction of second.

    What I did for this is -

    (((DT_I8)SUBSTRING(Field1,1,1) * 3600) + ((DT_I8)SUBSTRING(Field1,3,2) * 60) + ((DT_I8)SUBSTRING(Field1,6,2))) + ((DT_I8)(SUBSTRING(Field1,9,2)) / 100)

    If I add upto part (((DT_I8)SUBSTRING(Field1,1,1) * 3600) + ((DT_I8)SUBSTRING(Field1,3,2) * 60) + ((DT_I8)SUBSTRING(Field1,6,2))) then it works fine and gives me seconds as 64 but when I try to add fraction part ((DT_I8)(SUBSTRING(Field1,9,2)) / 100), it shows type case error. I tried to put Numeric and Decimal both for it but not working. :angry: :crazy:

    Does any one knows any simple way to calculate total seconds as mentioned above?

    :rolleyes:

  • September 24, 2010 at 1:58 pm

    #1227217

    SELECT DateDiff(ms, CONVERT(TIME, '00:00:00', 108), CONVERT(TIME, '0:01:04.50', 114)) / 1000.0

  • September 24, 2010 at 2:35 pm

    #1227243

    Hey Goran,

    I tried the way you told in SSIS Derived Column Transformation with little modification as per req. but it didn't work.

    DATEDIFF("Millisecond",(DT_DBTIME)"00:00:00",(DT_DBTIME)Field1) / 1000.0

    I tried data types as DT_DBTIME, DT_DBTIMESTAMP and DT_DATE ...

    Error - Error while evaluating function.!

    :rolleyes:

  • September 25, 2010 at 2:23 am

    #1227340

    Which type is Field1. Try with DT_DBTIME2

  • September 25, 2010 at 8:24 am

    #1227384

    Wll I tried that too.....

    the field is coming from text file and reading it as string as of now...

    :rolleyes:

  • September 29, 2010 at 8:11 am

    #1229047

    Well I got a root cause of this problem in my case...

    In typecasting it used to give me error becuase microsoft had released one patch for SQL Server 2005 which was installed on my system and it caused this issue. I dont have exact description of it but when the patch was updated with new patch provided by microsoft as they have accepted on their site that it was a bug in the old patch, it got resolved.

    I will provide more details on this like patch and stuff soon so if anyone faces issues with it can handle it..!!! 😎

    :rolleyes:

Viewing 6 posts - 1 through 5 (of 5 total)

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