I need a SQL server query to convert date to Mon DD, YYYY HH:MM:SS AM/PM

Question

I need a SQL server query to convert date to Mon DD, YYYY HH:MM:SS AM/PM

for e.g. the date is :- 2016-11-21 16:58:57.797

I need the SQL output as Nov 21 2016 4:58:57 PM

I tried --> CONVERT(VARCHAR(19),GETDATE(),109)
but it is displaying results like --> Nov 21 2016 4:58:5

Is is mysql or sql-server? It can't be both – Arion Nov 21 '16 at 07:23 — Arion, Nov 21 '16 at 07:23
Please tag with your RDBMS (product and version)! – Shnugo Nov 21 '16 at 07:25 — Shnugo, Nov 21 '16 at 07:25
See http://stackoverflow.com/a/11854877/68607 – TFD Nov 21 '16 at 07:29 — TFD, Nov 21 '16 at 07:29

score 1 · Answer 1 · answered Nov 21 '16 at 07:26

1

Increase the length of VARCHAR in your statement like below

select CONVERT(VARCHAR(30),GETDATE(),109)

This is giving me result as Nov 21 2016 12:55:31:390PM

Hope this should work out for you.

answered Nov 21 '16 at 07:26

Viki888

2,686
2
13
16

But I don't want the milisseconds to come up – Amar Kumar Nov 21 '16 at 07:33

score 0 · Answer 2 · answered Nov 21 '16 at 07:22

0

For mysql you can do this:

SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

For ms sql you can do this:

SELECT CONVERT(VARCHAR(19),GETDATE())

Reference:

answered Nov 21 '16 at 07:22

Arion

31,011
10
70
88

score 0 · Answer 3 · answered Nov 21 '16 at 07:42

Use SUBSTRING and CHARINDEX to get the left part of the string excluding the milliseconds part. Then concatenate the last AM/PM with that.

Query

declare @date as varchar(30) = convert(varchar(30),getdate(),109);
select substring(@date, 1, len(@date) - charindex(':', reverse(@date), 1)) 
       + ' ' + right(@date, 2) as [datetime];

score 0 · Answer 4 · answered Nov 21 '16 at 08:25

0

you can use this

select FORMAT(CAST(GETDATE() AS DATETIME),'MMM dd yyyy hh:mm:ss tt') AS DateTimeAMPM

answered Nov 21 '16 at 08:25

Kentgi

308
1
3
15

I need a SQL server query to convert date to Mon DD, YYYY HH:MM:SS AM/PM

4 Answers4