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

}

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

Please Note: At the place of ' ' you can add spaces or any separator like - or / etc.

Look at my output screen


Comments

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Customize User's Profile in ASP.NET Identity System