Solved

# CONVERT DATE TO MM/DD/YY(YY)

Posted on 2013-10-24
367 Views
i have the following date 2013-09-25 00:00:00.000 how can I convert it to

MM/DD/YY(YY) in the sql query
0
Question by:Star79
• 3
• 3
• 3
• +1

LVL 65

Expert Comment

SQL expert PortletPaul wrote an excellent article on SQL Server Date Styles using Convert, that demonstrates all styles.

The mm/dd/yy (and yyy) answer is
``````-- with the century
SELECT convert(varchar, GETDATE() ,1)

-- without the century
SELECT convert(varchar, GETDATE() ,101)
``````
Keep in mind that this is for display only.
0

Author Comment

Hello jim,
can the date be converted to MM/DD/YY(YY) .Please note the brackets
0

LVL 65

Assisted Solution

Jim Horn earned 250 total points
It could, but there's no single function to pull that off, so you'll need to do a lot of parsing...
``````Declare @dt datetime = GETDATE()

SELECT
RIGHT('0' + CAST(MONTH(@dt) as varchar(2)),2) + '/'	+
RIGHT('0' + CAST(DAY(@dt) as varchar(2)),2) + '/' +
CAST(YEAR(@dt) / 100 as varchar(2)) + '(' + RIGHT(CAST(YEAR(@dt) as CHAR(4)),2) + ')'
``````
0

LVL 26

Accepted Solution

Zberteoc earned 250 total points
SELECT STUFF(convert(varchar, GETDATE(),101),9,0,'(')+')'

101 conversion is always MM/DD/YYYY

with double digits for MM and DD
0

LVL 48

Expert Comment

2013-09-25 00:00:00.000 as "MM/DD/YY(YY)"

So you expect? 09/25/20(13) ... is that correct? I think the following would be simpler

``````DECLARE @dt datetime = GETDATE()

SELECT
convert(varchar(8),@dt ,103)
+ '('
+ convert(varchar(2),@dt ,12)
+ ')'
``````
0

LVL 48

Expert Comment

:) marginally easier in sql 2012
``````DECLARE @dt datetime = GETDATE()

SELECT
convert(varchar(8),@dt ,103) + '(' + convert(varchar(2),@dt ,12) + ')'  AS "MM/DD/YY(YY) sql2000 +"

, left(FORMAT(@dt, 'MM/dd/yyyy'),8) + FORMAT(@dt, '(yy)')                 AS "MM/DD/YY(YY) sql2012 +"
``````
0

LVL 26

Expert Comment

And how is that simpler then my solution?
0

LVL 48

Expert Comment

Sorry Zbertoc, no offence intended - it's an opinion only. Nothing wrong with yours.
0

LVL 26

Expert Comment

I wasn't offended at all I just wanted to know the reasoning behind that opinion. :)
0

LVL 65

Expert Comment

Note to self:  I must get in the habit of using STUFF() more often, as the above one-line solution demonstrates.
0

## Featured Post

### Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.