2

this is not duplicate of How to get time part from SQL Server 2005 datetime in 'HH:mm tt' format

be cause all answers of that question returns 12:06PM (without space and need space)

I am trying to get only time part from SQL GETDATE()

and I am trying 

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

but it is returning 

12:06PM

I need 12:06 PM (Space before AM or PM)..

search a lot but failed...

Community
  • 1
  • 1
Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
  • 2
    possible duplicate of [How to get time part from Sql Server 2005 datetime in HH:mm tt format](http://stackoverflow.com/questions/3201432/how-to-get-time-part-from-sql-server-2005-datetime-in-hhmm-tt-format) – Sami Kuhmonen May 21 '15 at 06:53
  • @samikuhmonen this is not duplicate of http://stackoverflow.com/questions/3201432/how-to-get-time-part-from-sql-server-2005-datetime-in-hhmm-tt-format, and mentioned the reason – Imran Ali Khan May 21 '15 at 07:00
  • 2
    Usually formatting should not be done on the DB layer, but on top of that. Is there a specific reason for doing it in the DB? – Sami Kuhmonen May 21 '15 at 07:01

6 Answers6

6

For SQL 2012 above:

SELECT FORMAT(GETDATE(), 'h:mm tt', 'en-US')
Eric
  • 5,675
  • 16
  • 24
3

Try this

SELECT Replace(Replace(LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)),'PM',' PM'),'AM',' AM')
SimarjeetSingh Panghlia
  • 2,200
  • 13
  • 14
2

Try this:

SELECT substring(CONVERT(varchar(20), GetDate(), 9), 13, 5) + 
       ' ' + 
       substring(CONVERT(varchar(30), GetDate(), 9), 25, 2)
Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
Robert
  • 25,425
  • 8
  • 67
  • 81
2

You can use:

select CONVERT(VARCHAR(5), GETDATE(), 108) + ' ' + 
       RIGHT(CONVERT(VARCHAR(30), GETDATE(), 9),2)

SQL fiddle: http://sqlfiddle.com/#!6/a7540/2377

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
Irvin Dominin
  • 30,819
  • 9
  • 77
  • 111
1

After A lot Practice Shortest Answer I Get..

SELECT CONVERT(VARCHAR(11), 
              STUFF(RIGHT( 
              CONVERT(VARCHAR,GETDATE(),100 ) ,7),
              6, 0, ' '))

Thanks All

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
0

FORMAT(GETDATE(), 'h:mm tt', 'en-US')

Piyush Kukadiya
  • 1
  • 1
  • While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Derek Brown Jun 16 '18 at 10:44