Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a datetime field that I need to convert into the format DDMONYY (27Aug12). I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time. How can I easily convert a date to the format I need?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Instead of your hack with replace, why not just cast to the right data type? This doesn't need to be nvarchar and the only reason it contains time is because you included too many characters. You can of course apply your replace here as well (or you could do that at the client).

SELECT CONVERT(CHAR(11),GETDATE(),13)
UNION ALL
SELECT CONVERT(CHAR(11), DATEADD(DAY, 10, GETDATE()), 13);

Results:

27 Aug 2012
06 Sep 2012
share|improve this answer

After a little bit more research I found the answer on the Microsoft cast and convert page. The date format 106 is the closest format to what I desire, except that it has spaces in it. Adding a replace to get rid of the spaces gets it into the proper format.

replace(convert(nvarchar(30),DateToConvert,106),' ','')
share|improve this answer
    
Strictly, speaking, the 6 format specifier gives a closer result to what you are asking about than the 106 one does, but keeping the century may be a good idea. –  Andriy M Aug 27 '12 at 19:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.