CODEWITHSUNDEEP
sqlsql-servert-sql
ABI
ABI

Reputation: 21

How to write a query to select data with a date format 'DD-MMM-YYYY'?

I want to write a query in SELECT statement with the format 'DD-MMM-YYYY' eg:

'1-Jan-2019' or '23-Mar-2018'

Upvotes: 1

Views: 1016

Answers (4)

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

You can do that pretty easily with a Convert call in SQL to a numbered format. It will be close to what you want, but for your format exactly you will need to replace the spaces in the string with dashses by using a Replace call on the result of the convert.

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

Example:

select REPLACE(CONVERT(VARCHAR(20), GetDate(), 106), ' ', '-')

Upvotes: 0

Ilyes
Ilyes

Reputation: 14926

Simply

SELECT CONVERT(VARCHAR, GetDate(), 106)

Returns:

23 Jan 2019

See CAST and CONVERT where the Date and Time Styles

If you really want to return '-' separator, you can do as

SELECT REPLACE(CONVERT(VARCHAR, GetDate(), 106), ' ', '-')

Returns:

23-Jan-2019

Upvotes: 4

Alexander Volok
Alexander Volok

Reputation: 5940

You can leverage T-SQL FORMAT() to request exact format of the output and culture as you require:

SELECT FORMAT(GETDATE(), '%d-MMM-yyyy', 'en')

It is available since SQL Server 2012

However, better practice is to control format on an app level

Upvotes: 2

Thomas Koelle
Thomas Koelle

Reputation: 3752

If you drop the dashes e.g. '1 jan 2018' then that works out of the box. It is also a really nifty format that always works if you don't know the database settings.

Just remember that month names are abbriviated differently for different languages.

Upvotes: 1

Related Questions