1

I see similar questions, but most have to do with datetime or fairly different requirements.

I have a CSV report that has a time column stored as HH:MM[:SS] (if over 1 day, it adds :00 for seconds... annoyingly, and I don't have control over how the report itself runs.) I think, in the derived column task, we can drop seconds.

Examples:

  • 23:54 = 23H 54M
  • 27:35:00 = 27H 35M 00S

My questions are:

  1. If the CSV loads the column as text, what expression do I need for the derived column?
  2. What should I set the data type as in the SQL database?

Pertinent info: SQL Server 2014 and SSDT for VS 2013

Thank you, Wes

2

2) Time/Date datatypes are often problematic choices for storing durations. Instead use an integer or decimal for whatever resolution is appropriate (seconds or milliseconds are popular, but minutes may work for you in this case).

1) In this instance, the simplest option may be: (DT_I4)TOKEN(Column,":",1) * 60 + (DT_I4)TOKEN(Column,":",2).

Other methods:

  • If you will always have two characters each for hours and minutes, you can use something like (DT_I4)SUBSTRING( [Column], 1, 2 ) * 60 + (DT_I4)SUBSTRING( [Column], 4, 2 ).
  • If hours can be 1 or more characters, but minutes is always two: (DT_I4)SUBSTRING(Column,1,FINDSTRING(Column,":",1) - 1) * 60 + (DT_I4)SUBSTRING(Column,FINDSTRING(Column,":",1) + 1,2)
  • Thanks. Would this handle the inconsistency of having, at times, the :00 second portion tacked on? I agree about storing it as an INT in raw minutes. – Wes Mar 21 '18 at 16:56
  • 1
    The SUBSTRINGs are specified for characters 1 & 2 and 4 & 5. Anything outside of that is ignored. Note that this assumes the hours portion will have a leading zero and will never be more than two characters. If that's a faulty assumption, you may need to use FINDSTRING to parse it out. – SQLFox Mar 21 '18 at 17:01
  • OK. that makes sense. From the looks of it, the only two formats are hh:MM and hh:MM:SS, so that should work perfectly. I really appreciate it. I will implement this and (upon it working) accept your answer. Thanks! – Wes Mar 21 '18 at 17:03
  • OOps... I take that all back. under 10 hours does not have a leading 0 and the time can be greater than 99 hours (leading to hhh:MM)... shoot. – Wes Mar 21 '18 at 17:08
  • 1
    Thanks! That worked perfectly. I also found, for the hour part, the 'Token' function works well... found that while trying to hack my way through this. – Wes Mar 21 '18 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.