Specific datetime format

Question

Is there a datetime format when using convert that will result in...mm/dd/yyyy hh:mm:ss AMPM?

Answer 1

select format(getdate(),'MM/dd/yyyy hh:mm:ss tt')

--

select convert(varchar(10),getdate(),101) +' '+ Stuff(Stuff(convert(varchar(26),getdate(),109),21,4,' '),1,12,'')

Answer 2

No for CONVERT function. You may use FORMAT() function in SQL Server 2012 and up.

See also

Formatting the time from a datetime or time datatype by using the STUFF function

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

FORMAT will do what you want but be aware performance of FORMAT is extremely poor relative to CONVERT. It would be best to offload display formatting to another application layer where possible.

Answer 4

Hi Kirkee,

Here's another workaround.

SELECT STUFF(CONVERT(CHAR(20), GETDATE(), 22), 7, 2, YEAR(GETDATE()));

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 5

SET NOCOUNT ON
DECLARE @startTime DATETIME = CURRENT_TIMESTAMP
DECLARE @dts TABLE (datetime DATETIME)
WHILE (SELECT COUNT(*) FROM @dts) < 10000
BEGIN
 INSERT INTO @dts (datetime) VALUES
 (CURRENT_TIMESTAMP+(RAND()-RAND()))
END
SET NOCOUNT OFF
PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR) +' to insert'
SET @startTime = CURRENT_TIMESTAMP

SELECT CONVERT(VARCHAR,datetime,101)+' '+STUFF(REPLACE(RIGHT(CONVERT(VARCHAR,datetime,109),14),' ','0'),9,4,' ')
  FROM @dts

PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR)  +' to select composite'
SET @startTime = CURRENT_TIMESTAMP

SELECT FORMAT(datetime, 'MM/dd/yyyy hh:mm:ss tt')
  FROM @dts

PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR)  +' to select format()'

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

Answer 6

Thanks for all the great options and advice on performance!

Answer 7

You're welcome!

Please don't forget to close your thread by marking an answer (even if it's something you post).

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.