Converting datetime to month-year only - T-SQL

Question

I want to convert a dateTime value to Month-Year (note the (-) between them).

i want them like Jan-2014, Feb-2014, Mar-2014, Apr-2014.

i have tried

SELECT convert(varchar(7), getdate(), 126) 
SELECT right(convert(varchar, getdate(), 106), 8) 

But the first line gives me 2014-05 (i need it like May-2014) while the second line gives May 2014 (its missing the -, as in May-2014)

Solution 1

You were almost there:

SELECT replace(right(convert(varchar, getdate(), 106), 8), ' ', '-')

Solution 2

i think convert my not be the correct approach. try this:

select substring(DATENAME(M,GETDATE()),1,3)+'-'+DATENAME(YYYY,GETDATE())

Solution 3

Write as:

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11),
                     GETDATE(), 106), 8),
                     ' ', '-') AS [Mon-YYYY]

Comments

[StackTrace]

so is the resulting value a datetime or a string?

[StackTrace]

Do u know the equivalent of the same for micorosoft access database engine? In SQL server it runs ok, in Access it gives undefined function convert.

[dean]

Sorry, I know nothing about Access. Probably you can use some VBA function (it's Format, I believe).