How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2

sql-server-2008 date select date-format
Saquibul Islam Waheed picture Saquibul Islam Waheed · Aug 13, 2013 · Viewed 176k times · Source

I am using CONVERT(data_type(length),expression,style) function to change the format of a date in a SELECT query.

Declare @dt nvarchar(20)

Select @dt = Convert(nvarchar(20), SalesDate, 113) FROM SalesTable

The format I need is 'dd-MMM-yyyy' (eg. '05-Jul-2013') but I could not find the proper style number (eg. 113) for this particular format. Can any one help me with that please?

Answer

Abhishek Jain picture Abhishek Jain · Aug 13, 2013

Try this:

Declare @dt NVARCHAR(20)

Select 
    @dt = REPLACE(CONVERT(CHAR(15), SalesDate, 106),' ',' - ') 
FROM SalesTable

Related questions

SQL SERVER: Get total days between two dates

I'm trying to get the total number of days between two days: 1/1/2011 3/1/2011 RETURN 62 Is it possible to do in SQL Server?

Read More
MS SQL compare dates?

I have 2 dates (datetimes): date1 = 2010-12-31 15:13:48.593 date2 = 2010-12-31 00:00:00.000 Its the same day, just different times. Comparing date1 and date2 using <= doesnt work because of the date1 time. So date1 <= date2 is wrong, but should be true. …

Read More
Get first day of week in SQL Server

I am trying to group records by week, storing the aggregated date as the first day of the week. However, the standard technique I use for rounding off dates does not appear to work correctly with weeks (though it does …

Read More