Change format Numeric to mmm-yy

Question

Change format Numeric to mmm-yy

Vitor da Fonseca

SSC-Addicted

Points: 465
More actions
November 22, 2013 at 7:48 am

#286568

Hi,
I have the following statement to give me the month
Month(DateAdd('m',-3,[Enter_Month])) AS month4
which gives me the month number e.g. 1, 2, 3, etc how can i format this statment to give me as mmm-yy (Jan-13, Feb-13, Mar-13, etc)
Any help is very much appreciated
Thanks in advance,
All help and Any help is appreciated

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Vitor da Fonseca (11/22/2013)
Hi,
I have the following statement to give me the month
Month(DateAdd('m',-3,[Enter_Month])) AS month4
which gives me the month number e.g. 1, 2, 3, etc how can i format this statment to give me as mmm-yy (Jan-13, Feb-13, Mar-13, etc)
Any help is very much appreciated
Thanks in advance,

Well you will have to get rid of the Month portion first right?

That leaves us with DateAdd that doesn't quite work as posted. You can't have the datepart be a string. I would recommend not using the shortcut codes and spelling it out.

That leaves us something like this:

select DateAdd(month, -3, Enter_Month) AS month4

My recommendation would be to leave the formatting to the front end instead of trying to do it in sql. If you have no other choice then you will need to take a look at CONVERT to get the format you desire.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

As CONVERT won't give you the format you require, you need to play with the string returned.

SELECT REPLACE( RIGHT( CONVERT( varchar(9), GETDATE(), 6), 6), ' ', '-'),

STUFF( RIGHT( CONVERT( varchar(11), GETDATE(), 13), 8), 4, 3, '-')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2