Help needed in time conversion

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  November 15, 2016 at 11:26 pm

  #332130

  Hi,

  declare @val varchar (10) = "20:11";

  How do i display this as 20 hours and 11 minutes. If it is 00:20 then 20 minutes. If it's 20:00 then 20 hours. If its 20 then also 20 hours. Any sample please how to achieve this.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 16, 2016 at 12:43 am

  #1912696

  KGJ-Dev (11/15/2016)

  ---

  Hi,

  declare @val varchar (10) = "20:11";

  How do i display this as 20 hours and 11 minutes. If it is 00:20 then 20 minutes. If it's 20:00 then 20 hours. If its 20 then also 20 hours. Any sample please how to achieve this.

  Quick suggestion

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  ;WITH SAMPLE_DATA(TIMESTR) AS

  (

  SELECT '00:10' UNION ALL

  SELECT '01:01' UNION ALL

  SELECT '02:01' UNION ALL

  SELECT '10:02' UNION ALL

  SELECT '12:00' UNION ALL

  SELECT '24:59'

  )

  SELECT

  SD.TIMESTR

  ,CASE

  WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) = 0 THEN ''

  WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) = 1 THEN '1 hour'

  WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) > 1 THEN LEFT(SD.TIMESTR,2) + ' hours'

  END +

  CASE

  WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) > 0 AND CONVERT(INT,RIGHT(SD.TIMESTR,2),0) > 0 THEN ' and '

  ELSE ''

  END +

  CASE

  WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) = 0 THEN ''

  WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) = 1 THEN '1 minute'

  WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) > 1 THEN RIGHT(SD.TIMESTR,2) + ' minutes'

  END AS TIME_OUT

  FROM SAMPLE_DATA SD;

  Output

  TIMESTR TIME_OUT

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

  00:10 10 minutes

  01:01 1 hour and 1 minute

  02:01 02 hours and 1 minute

  10:02 10 hours and 02 minutes

  12:00 12 hours

  24:59 24 hours and 59 minutes

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  November 16, 2016 at 7:15 am

  #1912781

  thank you Erik.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 16, 2016 at 8:02 am

  #1912804

  If the input string is limited to less than 24 hours, the following makes life pretty easy and will also produce an error if someone entered improper information that result in more than 24 hours or more than 59 minutes.

  WITH SAMPLE_DATA(TIMESTR) AS

  (

  SELECT '00:10' UNION ALL

  SELECT '01:01' UNION ALL

  SELECT '02:01' UNION ALL

  SELECT '10:02' UNION ALL

  SELECT '12:00' UNION ALL

  SELECT '23:59'

  )

  SELECT DATENAME(hh,TimeStr) + ' Hour(s) and ' + DATENAME(mi, TimeStr) + ' Minute(s)'

  FROM Sample_Data

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 16, 2016 at 8:16 am

  #1912810

  Jeff Moden (11/16/2016)

  ---

  If the input string is limited to less than 24 hours, the following makes life pretty easy and will also produce an error if someone entered improper information that result in more than 24 hours or more than 59 minutes.

  WITH SAMPLE_DATA(TIMESTR) AS

  (

  SELECT '00:10' UNION ALL

  SELECT '01:01' UNION ALL

  SELECT '02:01' UNION ALL

  SELECT '10:02' UNION ALL

  SELECT '12:00' UNION ALL

  SELECT '23:59'

  )

  SELECT DATENAME(hh,TimeStr) + ' Hour(s) and ' + DATENAME(mi, TimeStr) + ' Minute(s)'

  FROM Sample_Data

  ;

  Good point Jeff

  😎

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  November 16, 2016 at 8:17 am

  #1912811

  KGJ-Dev (11/16/2016)

  ---

  thank you Erik.

  You are very welcome.

  😎

  You should consider Jeff's fine advice, it is easy to incorporate into the solution.