date format to this MM/YY

What is the best way to make a date appear like this:

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
STUFF(CONVERT(varchar(10), date_value, 1), 4, 3, '')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
If your SQL Version is SQL 2012 or later you may use the more intuitive FORMAT() function e.g.

select FORMAT(getdate(),'MM/yy')

nb: the string used by format() is case sensitive, so you do need uppercase MM and lower case yy
Mark WillsTopic AdvisorCommented:
I would use style code 3 and pick up last 5 characters, or, stuff blank into first 3

using getdate() as a datetime datasource, here is an example
select convert(varchar(8),getdate(),3) as [date_style_3]
     , stuff(convert(varchar(8),getdate(),3),1,3,'') as [mm/yy with stuff]
     ,right(convert(varchar(8),getdate(),3),5) as [mm/yy last 5 char]

Open in new window

See : for convert and style codes.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
Not sure why anyone would choose the arcane world of style numbers over the more intuitive FORMAT() syntax. So if you use SQL 2012 or later I really do suggest FORMAT(your_data_here,'MM/yy')

But if you do have an earlier version of SQL Server you could use any style number that returns Month and Year in a predictable location e.g. styles 3, 4 or 5

PATTERN      STYLED DATE            SYNTAX                                     STYLE  LENGTH
DD MM YY     23/02/01               convert(varchar(8), your_data_here ,3)     3      8
DD MM YY     23.02.01               convert(varchar(8), your_data_here ,4)     4      8
DD MM YY     23-02-01               convert(varchar(8), your_data_here ,5)     5      8

Open in new window

from: SQL Server Date Styles (formats) using CONVERT()

"Style 3" does have the slash and the mm yy in the wanted order

Syntax comparison of approaches:

, STUFF(CONVERT(varchar(10),getdate(), 1), 4, 3, '')
, right(convert(varchar(8),getdate(),3),5)

Open in new window

Mark WillsTopic AdvisorCommented:

Agree wholeheartedly - just that that vbnetcoder selected 2008 as a TA.

So, used 2008 compatible constructs.

You know I love the Format() clause.

Even wrote an Article about it :)
vbnetcoderAuthor Commented:
Scott PletcherSenior DBACommented:
Not sure why anyone would choose the arcane world of style numbers over the more intuitive FORMAT() syntax.

Performance.  FORMAT seems to be noticeably slower.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.