Is there a function to format the date like this ?

Question

Post reply

Is there a function to format the date like this ?

mw_sql_developer

SSCoach

Points: 19445
More actions
June 27, 2016 at 9:29 am

#327404

I need a datetime field to report the value as '12/5/2016' or '3/2/2016'
( Takeway leading zeros from month and day )
The following works fine. But I wonder is there a short function already
to do that ... ?
This works fine
Declare @xx Datetime;
Set @xx = '02/05/2016'
SelectCASE WHEN LEFT (Replace((Convert(CHAR(10), @xx, 101 )), '/0', '/' ),1) = '0' THEN
SUBSTRING( (Replace((Convert(CHAR(10), @xx, 101 )), '/0', '/' )) ,2, 200 )
ELSE
Replace((Convert(CHAR(10), @xx, 101 )), '/0', '/' )
END

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Not a big improvement, but might help.

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

I would also like to say that hopefully this is for display purposes only (which could instead be handled by the client side application). Whatever you do, make sure you don't actually save these values back into your database. It will eventually come back to haunt you.

SELECT quote FROM brain WHERE original = 1
0 rows returned

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

YB makes a VERY key point: formatting stuff should be done outside SQL Server where at all possible!!! Don't waste any of the precious and expensive resources on your SQL Server doing that type of stuff!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

YB:

This is not for display purposes.

It is for a TAB delimited file that goes to another company.

I am not sure why they can not handle dates in the mm/dd/yyyy format.

I was given one of their sample files and I noticed dates in the m/d/yyyy format ( I mean the leading zeros gone for single digit dates and months )

It makes no sense.

Also there is no client side application when using this stuff with SSIS.

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

mw112009 (6/27/2016)
YB:
This is not for display purposes.
It is for a TAB delimited file that goes to another company.
I am not sure why they can not handle dates in the mm/dd/yyyy format.
I was given one of their sample files and I noticed dates in the m/d/yyyy format ( I mean the leading zeros gone for single digit dates and months )
It makes no sense.
Also there is no client side application when using this stuff with SSIS.

It shouldn't actually matter to the other company. Ask them instead of just going on based on a possible misunderstanding by someone else.

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