3

Just have a mssql query that has a date in the format:

'2016-03-22 00:00:00.000'

I need to format it as:

'22 March 2016'

I'm using SQL Server 2012. I've tried googling and the usual 106, 112 codes don't seem to work.

Is there a specific code format I can use?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Ang
  • 153
  • 1
  • 2
  • 9
  • I've also looked at using DATEPART but it seems unnecessarily long way of doing it. Just wondering if there is a easier way.. – Ang Mar 16 '16 at 04:48
  • Using `DATEPART`: `SELECT RIGHT('00' + CAST(DATEPART(DAY, GETDATE()) AS VARCHAR(2)), 2) + ' ' + DATENAME(MONTH, GETDATE()) + ' ' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4))` – Felix Pamittan Mar 16 '16 at 04:52
  • @Aug Check my answer it may helpful to you. – Krunal Mevada Mar 16 '16 at 05:24
  • There is no need write so long query so far just need `convert your date into datetime` and apply sql date format whatever format want to apply on it. – Krunal Mevada Mar 16 '16 at 05:30
  • [date time values have no display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Mar 16 '16 at 08:43

5 Answers5

3

Use Format Function in SQL Server 2012

SELECT FORMAT(GETDATE(),'dd-MMMM-yyyy')

See Image for your datatype ref enter image description here

Abdul Hannan Ijaz
  • 794
  • 6
  • 11
3

Try the FORMAT function:

SELECT FORMAT(GETDATE(), 'D', 'en-gb')

If your version does not support the FORMAT function, you can do it by concatenating the date parts:

SELECT 
    RIGHT('00' + CAST(DATEPART(DAY, GETDATE()) AS VARCHAR(2)), 2) + ' ' +
    DATENAME(MONTH, GETDATE()) + ' ' +
    CAST(DATEPART(YEAR, GETDATE())  AS VARCHAR(4))
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • i Upvoted this answer. But in reality your comment using DATEPART worked perfectly. Thank you. – Ang Mar 16 '16 at 05:15
1

Try this query:

SELECT CONVERT(VARCHAR, CONVERT(DATETIME, '2016-03-22 00:00:00.000'), 106)

You need to convert into DATETIME then you will get your desired output

More Datetime Sql formate

SQLFiddle

This may help you

Krunal Mevada
  • 1,637
  • 1
  • 17
  • 28
0

Try Convert function for SQL server:

select convert(varchar(11),cast('2016-03-22 00:00:00.000' as datetime), 106)
Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
0

How to change day, month, year in PHP:

echo $purchase_date=date('F-Y',strtotime('2022-03-22 00:00:00.000'));

Answer:

March-2022
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
indu surya
  • 1
  • 2
  • 1
    But the question is for SQL, not PHP – Jeremy Caney Sep 27 '22 at 02:52
  • This question is specifically tagged as a SQL question. Please avoid adding answers in other languages, as they add noise to the answers, and can cause confusion for others who have the same question later on. – Hoppeduppeanut Sep 29 '22 at 06:09