CODEWITHSUNDEEP
sql-serverformatting
Gkm
Gkm

Reputation: 247

How to convert date time format in SQL Server 2014

I'm using this query

SELECT convert(nvarchar(MAX), GETDATE(), 22) AS Date

Result: 08/05/16 12:23:08 PM

But I want result like this 8/5/2016 12:23:08 PM

dd/mm/yyyy hh:mm:ss a

Upvotes: 0

Views: 1806

Answers (2)

Jonathon Ogden
Jonathon Ogden

Reputation: 1582

As of SQL Server 2012 the FORMAT function is available allowing you to specify the format of data types and is locale-aware so it will consider date formatting in relation to the session's language or optional culture parameter.

You can achieve your custom formatting like so: FORMAT(GETDATE(), 'd/M/yyyy hh:mm:ss tt')

Note your requested format dd/mm/yyyy hh:mm:ss a is incorrect as in the case of single digits you want to remove zero padding i.e. 10/8/2016 not 10/08/2016. That's why in the format string I use only d and M.

Also, pay attention to @GarethD comment about the cost on larger datasets.

Upvotes: 5

Rom Eh
Rom Eh

Reputation: 2063

You could use the FORMAT function in T-SQL : https://msdn.microsoft.com/en-us/library/hh213505(v=sql.120).aspx

Here is the code :

SELECT FORMAT(GETDATE(), 'd/M/yyyy hh:mm:ss tt')

Upvotes: 1

Related Questions