• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 29
  • Last Modified:

date format to this MM/YY

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

01/18
0
vbnetcoder
Asked:
vbnetcoder
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Scott PletcherSenior DBACommented:
STUFF(CONVERT(varchar(10), date_value, 1), 4, 3, '')
1
 
PortletPaulfreelancerCommented:
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
0
 
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 : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles for convert and style codes.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
PortletPaulfreelancerCommented:
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:
select
  format(getdate(),'MM/yy')

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

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
@Paul,

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 :)

https://www.experts-exchange.com/articles/20059/T-SQL-formatting-Dates-and-Times-using-FORMAT.html
0
 
vbnetcoderAuthor Commented:
ty
0
 
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.
0

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now