*As of SQL Server 2012+*, you can use [<code>FORMAT(<i>value, format [, culture ]</i>)</code>][1]

Where the `format` param takes any valid [standard format string][2] or [custom formatting string][3]

**Example**:

```sql
SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy')
```

**Further Reading**: 

* [How to convert DateTime to VarChar][4]
* [How to convert date to a format `mm/dd/yyyy`][5]
* [sql server convert date to string MM/DD/YYYY][6]

[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
[2]: https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
[3]: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

[4]: https://stackoverflow.com/q/74385/1366033
[5]: https://stackoverflow.com/q/18568410/1366033
[6]: https://stackoverflow.com/q/11854775/1366033

I'm using following line of code in SQL:

    REPLACE(CONVERT(VARCHAR(11),getdate(),106), ' ','-') 

to convert given date to `MMM-DD-YYYY` format, this code is working perfectly fine if current date is more that 10th. 

But I'm getting error when its tries to convert single digit date, e.g when I'm converting date `9 jan 2014`. It is converted to `Jan--9-2014`.

Which is actually wrong as its contain one extra ` - `. 

Please help me to resolve this issue.

converting date to DD-MMM-YYYY format

Answer