November 16, 2016 at 12:43 am
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
November 16, 2016 at 7:15 am
thank you Erik.
November 16, 2016 at 8:02 am
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
Change is inevitable... Change for the better is not.
November 16, 2016 at 8:16 am
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
😎
November 16, 2016 at 8:17 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply