Seconds to AM/PM Time

Question

Post reply

Seconds to AM/PM Time

sqldummy79

SSC-Addicted

Points: 491
More actions
June 15, 2015 at 11:25 am

#309108

I have tried to find a blog or forum for the following issue, if someone could provide a link or the code, it would be greatly appreciated.
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day.
Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
Thank you for the assistance and I apologize for the duplicate post, since I'm sure there is something out here already answering this question

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

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

Y.B. SSChampion Points: 11649 More actions · Answer 1

You could do something like this. Just be mindful if you have any values that exceeds 24hrs (in seconds).

DECLARE @seconds INT

SET @seconds = 24331

IF @seconds < 43200

SELECT CAST(@seconds/3600 AS VARCHAR) + ':' + RIGHT('00' + CAST(@seconds%3600/60 AS VARCHAR),2) + ':' + RIGHT('00' + CAST(@seconds%60 AS VARCHAR),2) + ' AM' AS [time]

ELSE

SELECT CAST((@seconds/3600)-12 AS VARCHAR) + ':' + RIGHT('00' + CAST(@seconds%3600/60 AS VARCHAR),2) + ':' + RIGHT('00' + CAST(@seconds%60 AS VARCHAR),2) + ' PM' AS [time]

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's 86400 seconds in the day.

anything prior to exactly half that, (12:00:00) , or 43200 seconds, is AM, else PM

so a CASE WHEN seconds <43200 THEN 'AM' ELSE 'PM' END would work.

if you are using actual time or datetime datatypes, you can use the convert functions to show it, or use dateadd functions and use the math from above.

for example SELECT CONVERT(VARCHAR,getdate(),109) contains the AM/PM indicator for a datetime field.

what is your source of the seconds?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgmunson SSC Guru Points: 110640 More actions · Answer 3

sqldummy79 (6/15/2015)
I have tried to find a blog or forum for the following issue, if someone could provide a link or the code, it would be greatly appreciated.
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day.
Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
Thank you for the assistance and I apologize for the duplicate post, since I'm sure there is something out here already answering this question

If you can guarantee a value that never exceeds 86,400 then you can use the following:

WITH INT_VALUES AS (

SELECT SecondsValue = 23641 UNION ALL

SELECT 43641

)

SELECT SUBSTRING(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 13, 8) + ' ' +

RIGHT(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 2) AS TIME_VALUE

FROM INT_VALUES;

EDIT: fixed the SQL where a couple of typos lopped off a bunch of the code and changed the style number for the CONVERT.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TomThomson SSC Guru Points: 104773 More actions · Answer 4

sgmunson (6/15/2015)
sqldummy79 (6/15/2015)
I have tried to find a blog or forum for the following issue, if someone could provide a link or the code, it would be greatly appreciated.
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day.
Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
Thank you for the assistance and I apologize for the duplicate post, since I'm sure there is something out here already answering this question
If you can guarantee a value that never exceeds 86,400 then you can use the following:
WITH INT_VALUES AS (
SELECT SecondsValue = 23641 UNION ALL
SELECT 43641
)
SELECT SUBSTRING(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 13, 8) + ' ' +
RIGHT(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 2) AS TIME_VALUE
FROM INT_VALUES;
EDIT: fixed the SQL where a couple of typos lopped off a bunch of the code and changed the style number for the CONVERT.

There's no need to stick to less than 86,400 seconds. Your code works fine for higher values too.

An alternative to using division and cast is to use a datetime constand and addition:-

WITH INT_VALUES AS (

SELECT SecondsValue = 23641 UNION ALL

SELECT 43641 UNION ALL

SELECT 110041 UNION ALL

SELECT 130041

)SELECT SUBSTRING(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),100), 12, 8) AS TIME_VALUE

FROM INT_VALUES

I think it's a bit more obvious what's happening in this version.

Tom

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

TomThomson (6/15/2015)
sgmunson (6/15/2015)
sqldummy79 (6/15/2015)
I have tried to find a blog or forum for the following issue, if someone could provide a link or the code, it would be greatly appreciated.
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day.
Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
Thank you for the assistance and I apologize for the duplicate post, since I'm sure there is something out here already answering this question
If you can guarantee a value that never exceeds 86,400 then you can use the following:
WITH INT_VALUES AS (
SELECT SecondsValue = 23641 UNION ALL
SELECT 43641
)
SELECT SUBSTRING(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 13, 8) + ' ' +
RIGHT(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),109), 2) AS TIME_VALUE
FROM INT_VALUES;
EDIT: fixed the SQL where a couple of typos lopped off a bunch of the code and changed the style number for the CONVERT.
There's no need to stick to less than 86,400 seconds. Your code works fine for higher values too.
An alternative to using division and cast is to use a datetime constand and addition:-
WITH INT_VALUES AS (
SELECT SecondsValue = 23641 UNION ALL
SELECT 43641 UNION ALL
SELECT 110041 UNION ALL
SELECT 130041
)SELECT SUBSTRING(CONVERT(VARCHAR, CAST(SecondsValue / 86400. AS datetime),100), 12, 8) AS TIME_VALUE
FROM INT_VALUES
I think it's a bit more obvious what's happening in this version.

Or this:

WITH INT_VALUES AS (

SELECT SecondsValue = 23641 UNION ALL

SELECT 43641 UNION ALL

SELECT 110041 UNION ALL

SELECT 130041

)

select replace(right(convert(varchar,dateadd(second, SecondsValue % 86400,0), 109), 14),':000',' ') from INT_VALUES;