December 2, 2016 at 12:02 pm
Hello all,
We have time saved in our db in a seconds after midnight format and need to convert it to a regular AM/PM time format. Currently we use a function to convert to military time using the following:
DECLARE @timechar char(5)
SET @timechar = case @time
WHEN -1 THEN ''
WHEN 0 THEN ''
ELSE (right('0' + ltrim(str(@time/3600)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2))
END
RETURN (@timechar)
Can anyone help me to modify this so it's formatting it as, say, 07:30 PM rather than 19:30?
Any help would be much appreciated!
Thanks
December 2, 2016 at 12:31 pm
tacy.highland (12/2/2016)
Hello all,We have time saved in our db in a seconds after midnight format and need to convert it to a regular AM/PM time format. Currently we use a function to convert to military time using the following:
DECLARE @timechar char(5)
SET @timechar = case @time
WHEN -1 THEN ''
WHEN 0 THEN ''
ELSE (right('0' + ltrim(str(@time/3600)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2))
END
RETURN (@timechar)
Can anyone help me to modify this so it's formatting it as, say, 07:30 PM rather than 19:30?
Any help would be much appreciated!
Thanks
Since you are working with and storing the time in a non-standard SQL format, I'm not sure if there is a built-in "convert".
But you could just augment your case statement with another WHEN.
case when @time <= 0 then ''
when @time < 43000 then right('0' + ltrim(str(@time/3600)),2) + ':' +
right('0' + ltrim(str((@time%3600/60)),2)) + ' AM'
else right('0' + ltrim(str(@time/3600-12)),2) + ':' +
right('0' + ltrim(str((@time%3600/60)),2)) + ' PM'
end
I will leave the debate as to what designation midnight and noon should be, AM or PM for another day!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 2, 2016 at 12:54 pm
Here's an option using an inline table-valued function which would perform a lot better than a normal function. It also has a test to show how does it work.
The code is simple, but you should examine it piece by piece to understand what it is doing.
USE Test
GO
CREATE FUNCTION TimeCharFromInt(
@Time int
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT CASE WHEN @Time > 0
THEN STUFF( SUBSTRING( CONVERT(varchar(26), DATEADD(ss, @Time, 0), 109), 13, 16), 6, 7, '')
ELSE '' END TimeChar;
GO
SELECT number * 60, t.TimeChar
FROM master.dbo.spt_values
CROSS APPLY dbo.TimeCharFromInt( number*60) t
WHERE type = 'P'
GO
DROP FUNCTION TimeCharFromInt
December 2, 2016 at 1:12 pm
Another option...
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (n) AS (
SELECT TOP 86399-- 86400 would be midnight of the following day...
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4, n n5
),
cte_CastTime AS (
SELECT
SecondsPastMidnight = t.n,
FormattedStringTime = CAST(DATEADD(ss, CASE WHEN t.n >= 43200 THEN t.n - 43200 ELSE t.n END, CAST('00:00:00' AS TIME)) AS CHAR(8))
FROM
cte_Tally t
)
SELECT
ct.SecondsPastMidnight,
MilitaryTime = DATEADD(ss, ct.SecondsPastMidnight, CAST('00:00:00' AS TIME(0))),
FormattedStringTime = CASE
WHEN ct.SecondsPastMidnight < 3600 THEN STUFF(STUFF(ct.FormattedStringTime, 1, 2, '12'), 6, 3, ' AM')
WHEN ct.SecondsPastMidnight BETWEEN 43200 AND 46800 THEN STUFF(STUFF(ct.FormattedStringTime, 1, 2, '12'), 6, 3, ' PM')
WHEN ct.SecondsPastMidnight < 43200 THEN STUFF(ct.FormattedStringTime, 6, 3, ' AM')
ELSE STUFF(ct.FormattedStringTime, 6, 3, ' PM')
END
FROM
cte_CastTime ct
ORDER BY
ct.SecondsPastMidnight;
December 2, 2016 at 1:27 pm
Thanks everyone for your assistance! I've been working with LinksUp original option with a few modifications to handle the AM/PM for midnight and noon. I think this is it:
SET @timechar = case when @time <= 0 then ''
when @time < 43220 then right('0' + ltrim(str(@time/3600)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2) + ' AM'
when @time between 43220 and 46760 then right('0' + ltrim(str(@time/3600)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2) + ' PM'
when @time between 86400 and 89940 then right('0' + ltrim(str(@time/3600-12)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2) + ' AM'
else right('0' + ltrim(str(@time/3600-12)),2) + ':' + right('0' + ltrim(str((@time%3600)/60)),2) + ' PM'
end
Looking at the other options, I am not quite at that level yet to fully understand them... but I really appreciate everyone's help on this!
December 2, 2016 at 2:07 pm
Too much work. Looks like we are using SQL Server 2012 based on where this question is posted. The following will work on SQL Server 2008 and newer:
DECLARE @sec INT = 50096;
SELECT @sec;
SELECT
CAST(DATEADD(SECOND,@sec,0) AS TIME),
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END;
Needs just a little work to drop the leading 0.
December 2, 2016 at 2:19 pm
Updated:
DECLARE @sec INT;
SET @sec = 50096;
SELECT @sec;
SELECT
CAST(DATEADD(SECOND,@sec,0) AS TIME),
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END,
STUFF(
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END,
1,
PATINDEX('0[0-9]%',
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END),
'')
;
SET @sec = 80096;
SELECT @sec;
SELECT
CAST(DATEADD(SECOND,@sec,0) AS TIME) ComputedTime,
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END LeadingZeroTime,
STUFF(
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END,
1,
PATINDEX('0[0-9]%',
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END),
'') NoLeadingZeroTime
;
December 2, 2016 at 2:31 pm
To provide more background on this, our environment is a little different than expected so I had to take into account the early (1-3560 seconds) 12am times as well as the late (86400+ seconds) 12am times as they'll both show up in our results. I actually had to add another When statement to catch everything:
CASE WHEN @time <= 0 THEN ''
WHEN @time BETWEEN 1 AND 3560 THEN '12' + ':' + right('0' + ltrim(str((@time%3600)/60)),2) + ' AM'
WHEN @time BETWEEN 3620 AND 43160 THEN RIGHT('0' + ltrim(str(@time/3600)),2) + ':' + RIGHT('0' + ltrim(str((@time%3600)/60)),2) + ' AM'
WHEN @time BETWEEN 43220 AND 46760 THEN RIGHT('0' + ltrim(str(@time/3600)),2) + ':' + RIGHT('0' + ltrim(str((@time%3600)/60)),2) + ' PM'
WHEN @time BETWEEN 86400 AND 89940 THEN RIGHT('0' + ltrim(str(@time/3600-12)),2) + ':' + RIGHT('0' + ltrim(str((@time%3600)/60)),2) + ' AM'
ELSE RIGHT('0' + ltrim(str(@time/3600-12)),2) + ':' + RIGHT('0' + ltrim(str((@time%3600)/60)),2) + ' PM'
END
December 2, 2016 at 2:36 pm
Am I missing something, and if not, will this work?
SELECT CONVERT(varchar(20), CONVERT(time, DATEADD(SECOND, ,<Your field containing seconds>, CONVERT(datetime, CONVERT(date, getdate())))), 100)
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 2, 2016 at 2:38 pm
Lynn Pettis (12/2/2016)
Too much work. Looks like we are using SQL Server 2012 based on where this question is posted. The following will work on SQL Server 2008 and newer:
DECLARE @sec INT = 50096;
SELECT @sec;
SELECT
CAST(DATEADD(SECOND,@sec,0) AS TIME),
CASE WHEN @sec < 43000
THEN CAST(CAST(DATEADD(SECOND,@sec,0) AS TIME) AS VARCHAR(8)) + ' AM'
ELSE CAST(CAST(DATEADD(SECOND,@sec - 43000,0) AS TIME) AS VARCHAR(8)) + ' PM'
END;
Needs just a little work to drop the leading 0.
Just wondering why the use of 43000 as the number of seconds determining AM vs. PM? A full day is 86,400 seconds, so noon is exactly 43,200 seconds after midnight. Thus any value less than 43,200 is going to be an AM value and any value greater than or equal to 43,200 is a PM value, and the subtraction of 12 hours is 12 x 3,600 (secs/hr) or 43,200.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 2, 2016 at 2:43 pm
Michael L John (12/2/2016)
Am I missing something, and if not, will this work?
SELECT CONVERT(varchar(20), CONVERT(time, DATEADD(SECOND, ,<Your field containing seconds>, CONVERT(datetime, CONVERT(date, getdate())))), 100)
I was thinking along similar lines, but if storing the value as a real datetime or time, just convert it and call it quits. Then format the queried value as appropriate.
If you wanted to add a column to store the actual time, you could use Michael's function without the outer convert as the definition for a persisted computed column. Whenever your underlying column value changed, the time column would change.
December 2, 2016 at 2:47 pm
omg.
I'm taking that one.
Thank you, Michael!
December 2, 2016 at 3:26 pm
Michael L John (12/2/2016)
Am I missing something, and if not, will this work?
SELECT CONVERT(varchar(20), CONVERT(time, DATEADD(SECOND, ,<Your field containing seconds>, CONVERT(datetime, CONVERT(date, getdate())))), 100)
You're not missing a thing. I did similar...
DECLARE @Time INT;
SELECT @Time = 70200
;
SELECT REPLACE(RIGHT(CONVERT(CHAR(19),DATEADD(ss,@Time,0),100),7),' ','0')
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2016 at 3:44 pm
The only thing I have against these nice simple answers:
REPLACE(RIGHT(CONVERT(CHAR(19),DATEADD(ss,@Time,0),100),7),' ','0')
and
CONVERT(varchar(20), CONVERT(time, DATEADD(SECOND, @time, CONVERT(datetime, CONVERT(date, getdate())))), 100)
...is that they appear to display the time as 07:30PM rather than 07:30 PM (with a space preceding PM). How could that be addressed?
December 2, 2016 at 3:51 pm
tacy.highland (12/2/2016)
The only thing I have against these nice simple answers:
REPLACE(RIGHT(CONVERT(CHAR(19),DATEADD(ss,@Time,0),100),7),' ','0')
and
CONVERT(varchar(20), CONVERT(time, DATEADD(SECOND, @time, CONVERT(datetime, CONVERT(date, getdate())))), 100)
...is that they appear to display the time as 07:30PM rather than 07:30 PM (with a space preceding PM). How could that be addressed?
Use the STUFF function... STUFF(<method of choice>, 6, 0, ' ')
SELECT STUFF('07:30PM', 6, 0, ' ')
07:30 PM
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply