String conversion

Question

Post reply

String conversion

SQL_path

SSCrazy

Points: 2810
More actions
May 9, 2012 at 8:25 am

#255622

Hi,
How can I get the m/d/yy format instead of m/d/yyyy
rightnow my query looks like below:
select convert(varchar(2), month(getdate())) + '/'
+ convert(varchar(2), day(getdate())) + '/'
+ convert(varchar(4), year(getdate()))
then,
I tried below but I get * as year when I run it
select convert(varchar(2), month(getdate())) + '/'
+ convert(varchar(2), day(getdate())) + '/'
+ convert(varchar(2), year(getdate())) end
thanks !!

Viewing 14 posts - 1 through 14 (of 14 total)

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

Geoff A SSChampion Points: 11511 More actions · Answer 1

select convert(varchar(2), month(getdate())) + '/'

+ convert(varchar(2), day(getdate())) + '/'

+ RIGHT(convert(varchar(4), year(getdate())),2)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

This:

select convert(varchar(2), month(getdate())) + '/' + convert(varchar(2), day(getdate())) + '/' + RIGHT(DATENAME(yy,GETDATE()),2);

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

The BEST way to get date formatting is in the front end. 😛

If you must use sql than use Lynn's approach.

_______________________________________________________________

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/

SQL_path SSCrazy Points: 2810 More actions · Answer 4

SQL_path

SSCrazy

Points: 2810

May 9, 2012 at 10:07 am

#1485164

Thanks all for your replies !!

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 5

BWAAA-HAAA!!! This will do it! :-P:-D Makes a "great" interview question. :sick:

WITH F(x) AS (SELECT TOP (CHECKSUM(66-ASCII('A'))) '/'+CONVERT(CHAR(8),GETDATE(),CAST(EXP(0) AS INT)) FROM sys.objects)

SELECT STUFF(REPLACE(LEFT(x,POWER(73,1/2)*POWER(25,1/2.0)),'/0','/')

+ RIGHT(x,LOG10(10000)),CONVERT(INT,SIN(PI()/2)),0x47-70,SUBSTRING('xxx',-5,1))

FROM F;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

Jeff Moden (5/9/2012)
BWAAA-HAAA!!! This will do it! :-P:-D Makes a "great" interview question. :sick:
WITH F(x) AS (SELECT TOP (CHECKSUM(66-ASCII('A'))) '/'+CONVERT(CHAR(8),GETDATE(),CAST(EXP(0) AS INT)) FROM sys.objects)
SELECT STUFF(REPLACE(LEFT(x,POWER(73,1/2)*POWER(25,1/2.0)),'/0','/')
+ RIGHT(x,LOG10(10000)),CONVERT(INT,SIN(PI()/2)),0x47-70,SUBSTRING('xxx',-5,1))
FROM F;

Even though I think you're showing off, I have to ask.

Can I use this? :w00t:

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 7

dwain.c (5/9/2012)
Even though I think you're showing off, I have to ask.
Can I use this? :w00t:

Sure thing. The only price you have to pay is to tell me for what. 😛 I smell a good prank coming up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (5/9/2012)
BWAAA-HAAA!!! This will do it! :-P:-D Makes a "great" interview question. :sick:
WITH F(x) AS (SELECT TOP (CHECKSUM(66-ASCII('A'))) '/'+CONVERT(CHAR(8),GETDATE(),CAST(EXP(0) AS INT)) FROM sys.objects)
SELECT STUFF(REPLACE(LEFT(x,POWER(73,1/2)*POWER(25,1/2.0)),'/0','/')
+ RIGHT(x,LOG10(10000)),CONVERT(INT,SIN(PI()/2)),0x47-70,SUBSTRING('xxx',-5,1))
FROM F;

ROTFLOL! Whoever wrote this had way too much time on their hands!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

SELECT STUFF(REPLACE('/'+CONVERT(VARCHAR(10),GETDATE(),1),'/0','/'),1,1,'')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113563 More actions · Answer 10

if its a date time column could you not just do

select CONVERT(varchar,getdate(),10)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

anthony.green (5/10/2012)
if its a date time column could you not just do
select CONVERT(varchar,getdate(),10)

No leading zero for days and months...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 12

ChrisM@Work (5/10/2012)
SELECT STUFF(REPLACE('/'+CONVERT(VARCHAR(10),GETDATE(),1),'/0','/'),1,1,'')

That's what my original code looked like. The problem is that it knocks out the leading 0 on 2 digit years when they happen. Now, if the wanted 4 digit years, then that's the way to go! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_path SSCrazy Points: 2810 More actions · Answer 13

SQL_path

SSCrazy

Points: 2810

May 10, 2012 at 9:43 am

#1485795

thanks all