Time Format in SQL
Today I received a
question from a guy on SQL title, I'm not a SQL guy, but when I dive to solve
this, done.
Look at the question:-
{
SELECT LEFT(CONVERT(VARCHAR,
online_time,109),4)+' '+ right(convert(varchar, online_time,109),2) as
online_time from time
above is my query
i want output as
04:30 am 4:30 am will do(if initial 0 is removed no prob)
09:45 pm
11:15 am
my prob is that above query is giving right output for time before 10 am/pm but after 9:59 am/pm time is wrong i.e, it displays
11:3 pm instead of 11:30 pm
and give right for
4:40 pm
how i can do that
above is my query
i want output as
04:30 am 4:30 am will do(if initial 0 is removed no prob)
09:45 pm
11:15 am
my prob is that above query is giving right output for time before 10 am/pm but after 9:59 am/pm time is wrong i.e, it displays
11:3 pm instead of 11:30 pm
and give right for
4:40 pm
how i can do that
}
Now, look at my solution:-
SELECT
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6)
GO
--OUTPUT = 11:30
and if you want to add AM or PM then
SELECT
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) + ' ' +
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 25, 2) COLNAME
GO
--OUTPUT = 11:30 AM
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6)
GO
--OUTPUT = 11:30
and if you want to add AM or PM then
SELECT
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) + ' ' +
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 25, 2) COLNAME
GO
--OUTPUT = 11:30 AM
Please Note: At the place
of ' ' you can add spaces or any separator like - or / etc.
Look
at my output screen
Comments
Post a Comment