Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE

28 May 2012

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


No comments:

Post a Comment