CONVERT DATE TO MM/DD/YY(YY)

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
Star79Asked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
SELECT STUFF(convert(varchar, GETDATE(),101),9,0,'(')+')'

101 conversion is always MM/DD/YYYY

with double digits for MM and DD
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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)

Open in new window

Keep in mind that this is for display only.
0
 
Star79Author Commented:
Hello jim,
can the date be converted to MM/DD/YY(YY) .Please note the brackets
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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) + ')'

Open in new window

0
 
PortletPaulfreelancerCommented:
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)
      + ')'

Open in new window

0
 
PortletPaulfreelancerCommented:
:) 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 +"

Open in new window

0
 
ZberteocCommented:
And how is that simpler then my solution?
0
 
PortletPaulfreelancerCommented:
Sorry Zbertoc, no offence intended - it's an opinion only. Nothing wrong with yours.
0
 
ZberteocCommented:
I wasn't offended at all I just wanted to know the reasoning behind that opinion. :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Note to self:  I must get in the habit of using STUFF() more often, as the above one-line solution demonstrates.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.