2

I have the date in this format 2017-02-03 and I want that my date should be in this format 01-Mar-2016 while making select command.

I am using SQL Server 2012

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bilal
  • 17
  • 2
  • 6
  • Are we talking about a DateTime column here? – Thorsten Dittmar Sep 05 '17 at 11:21
  • A very similar Question which may help you: [Link](https://stackoverflow.com/questions/17205441/convert-date-format-into-dd-mmm-yyyy-format-in-sql-server) – MartinLeitgeb Sep 05 '17 at 11:23
  • your value in your first part is ambiguous. Are you talking about `YYYY-MM-DD` (ISO format) ? – Pac0 Sep 05 '17 at 11:23
  • 1
    If your dates are stored as dates, then they have no format. You *could* use something like `FORMAT(dateColumn, 'dd-MMM-yyyy')` **however**, formatting is almost always a job for the presentation layer, and not for SQL. Suppose you send this to presentation layer in this string format, and someone wants to sort the dates, only to find that `02-Dec-17` is coming out before `03-Jan-16`, but after `01-Feb-19`. Another consideration would be international users, by formatting on the client side you let their regional settings determine the date format, rather than your preference. – GarethD Sep 05 '17 at 11:30
  • 4
    If your dates are not stored as dates, then you [have a bigger problem on your hand](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type). Fix that first. – GarethD Sep 05 '17 at 11:31
  • it was in date format only...I got this from Format keyword – Bilal Sep 05 '17 at 13:39

2 Answers2

1

Check this website. This will be helpful.

select replace(convert(varchar, getdate(), 106),' ','-')

What is done here is: First convert it into '01 Mar 2016' and then replace white spaces(' ') with '-'. Which will give desired output as '01-Mar-2016'

Ayush Kesarwani
  • 518
  • 6
  • 18
  • 3
    [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – GarethD Sep 05 '17 at 11:32
1

Use SQL Server Format function where you can specify .NET format string

For instance for current date

SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy');

Also you can specify culture here if needed

SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy', 'en-US');

If you dates are stored as string you'd better fix this but you can just CAST or CONVERT them instead. I.e.

SELECT FORMAT(CAST('2017-10-11' AS date), 'dd-MMM-yyyy');
Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90