Convert DATETIME, trim leading zeros

Question

Convert DATETIME, trim leading zeros

Chrissy321

SSCoach

Points: 15763
More actions
January 25, 2012 at 7:14 pm

#250204

Hello All,
How can I alter this convert statement to return 1/25/2012 rather than 01/25/2012.
Or return 1/1/2012 rather than 01/01/2012
SELECT CONVERT(varchar(10),getdate(),101)
Thanks if you can help.

Viewing 15 posts - 1 through 15 (of 17 total)

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

umasingh Ten Centuries Points: 1182 More actions · Answer 1

Chrissy321 (1/25/2012)
Hello All,
How can I alter this convert statement to return 1/25/2012 rather than 01/25/2012.
Or return 1/1/2012 rather than 01/01/2012
SELECT CONVERT(varchar(10),getdate(),101)
Thanks if you can help.

This may help you.

Declare @x varchar(100)

SELECT @x=CONVERT(varchar(10),getdate(),101)

Select replace(left(@x,5),'0','')+RIGHT(@x,5)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

umasingh (1/25/2012)
Chrissy321 (1/25/2012)
Hello All,
How can I alter this convert statement to return 1/25/2012 rather than 01/25/2012.
Or return 1/1/2012 rather than 01/01/2012
SELECT CONVERT(varchar(10),getdate(),101)
Thanks if you can help.
This may help you.
Declare @x varchar(100)
SELECT @x=CONVERT(varchar(10),getdate(),101)
Select replace(left(@x,5),'0','')+RIGHT(@x,5)

Try the with '10/20/2012' as the date and see what you come up with. 😉

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

This should do it, Chrissy...

DECLARE @SomeExampleDate DATETIME;

SELECT @SomeExampleDate = '01/01/2012';

SELECT STUFF(REPLACE('/'+CONVERT(CHAR(10),@SomeExampleDate,101),'/0','/'),1,1,'')

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

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

Jeff Moden (1/25/2012)
This should do it, Chrissy...
DECLARE @SomeExampleDate DATETIME;
SELECT @SomeExampleDate = '01/01/2012';
SELECT STUFF(REPLACE('/'+CONVERT(CHAR(10),@SomeExampleDate,101),'/0','/'),1,1,'')

Don't you just hate it when a simple solution is staring you in the face and you just don't see it? :blush:

Nice one, Jeff.

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

Chrissy321

SSCoach

Points: 15763

January 26, 2012 at 8:11 am

#1438816

Thanks! I wasn't aware of STUFF

Jared SSC Guru Points: 61793 More actions · Answer 6

Now that you have your answer, Chrissy, can I ask why you are worrying about this formatting in SQL? Maybe there is a better way to handle the formatting on presentation versus in the query.

Jared
CE - Microsoft

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

Lynn Pettis (1/25/2012)
Jeff Moden (1/25/2012)
This should do it, Chrissy...
DECLARE @SomeExampleDate DATETIME;
SELECT @SomeExampleDate = '01/01/2012';
SELECT STUFF(REPLACE('/'+CONVERT(CHAR(10),@SomeExampleDate,101),'/0','/'),1,1,'')
Don't you just hate it when a simple solution is staring you in the face and you just don't see it? :blush:
Nice one, Jeff.

Heh... been there and done that. I get lucky sometimes. 🙂 thanks for the feedback, Lynn.

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 8

Chrissy321 (1/26/2012)
Thanks! I wasn't aware of STUFF

You bet. We could have just as easily used SUBSTRING(expression,2,10) instead of STUFF for this but glad I could introduce you to a new function.

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

Forgot to ask... Jared is correct on this. This type of formatting is usually relegated to the frontend code and I'm as curious as he. Why did you need to do this on the SQL Server side of the house?

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

Chrissy321 (1/25/2012)
How can I alter this convert statement to return 1/25/2012 rather than 01/25/2012.
Or return 1/1/2012 rather than 01/01/2012
SELECT CONVERT(varchar(10),getdate(),101)

Although formatting is generally best done elsewhere, SQL Server 2012 introduces a feature to make this much easier in T-SQL:

SELECT FORMAT(GETDATE(), 'd', 'en-US');

That specifies short date format for the en-US locale, and gives the results you are after.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Chrissy321 SSCoach Points: 15763 More actions · Answer 11

I do sometimes format in tsql. Its quick and dirty if you want to send out an sp_send_dbmail email using an html string in which case there is no front-end to do the formatting in.

I may format in tsql if say I'm developing for an SSIS package and the requirements require a specific date format. In cases like this I'd rather modify a proc rather than dig into a package. So i'll define a string like 20120126.csv and pass that to a variable via DTEXEC rather than dynamically building the file name in the package.

In this case the front-end is SSRS so I could probably have done the formatting there but generally in my SSRS development my principle has been to deliver the data to SSRS in the final format. I'm pretty much a newbie in SSRS so to expdite matters I'll format sever-side

If I was developmg a commercial app for international consumption then sure the formatting would need to take advantage of local settings. In my case my audience is internal and captive and specific about what they want to see.

Another consideration is the people who support my development, my backup. They are stronger in tsql.

So it comes down to ease of modification, consolidation of logic server-side, my skills, my teams skills and my intended audience.

I try to get everyone to use YYYYMMDD but they rebel!

Thanks to all on these forums! I have learned so much.

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

Thanks, Chrissy. I always like to know these types of things so I can help better. I appreciate the time you spent on the reply.

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

Jared SSC Guru Points: 61793 More actions · Answer 13

Hi Chrissy,

It sounds like you have a lot of experience, so I don't want to dwell on this... but in the long term, as your data grows, formatting before SSRS "may" hit performance significantly on large sets. Of course every situation is different, but in general, I find it always better to not format it in SQL server. You may not realize it now, but you sure will when someone asks you to change it which will require you to change it in both SQL and SSRS in most cases. Just my 2 cents. Good luck to you!

Jared
CE - Microsoft

thisisfutile Hall of Fame Points: 3668 More actions · Answer 14

I sure hope this isn't considered bad etiquette by reviving a 5 year old thread but the reply button was available so... 😉

I'm like Crissy, I'm stuck in Tsql so I'll need to formate a date using functions. Does anyone have an opinion concerning speed/performance on Paul White's excellent idea of 'FORMAT' (I'm on 2012 and wasn't aware of this function) versus Jeff's suggestions of STUFF or SUBSTRING?

Thank you!