Format SQL date mm/dd/yy with no leading zeros except for year.

I would like to find a more concise way to format a date in SQL as a mm/dd/yy format with no leading zeros, except for the year.  For example, July 4 2009 would be rendered as 7/4/09.

I'm familiar with using style values with the Convert() method (http://msdn.microsoft.com/en-us/library/ms187928.aspx), but no style appears to do exactly what I want.

Below is some sample code that I'm working with.

DECLARE @Date smalldatetime
SET @Date = '7/4/2009'

-- This produces 07/04/09
PRINT CONVERT(varchar(8),@Date,1)

-- Produces 7/4/2009.  This removes leading zeros and is good if I want a 4 digit year.
PRINT REPLACE(REPLACE('//' + CONVERT(varchar, @Date, 101), '/0', '/'), '//', '')

-- Produces 7/4/9.  This removes all leading zeros, including that of the year.
PRINT REPLACE(REPLACE('//' + CONVERT(varchar, @Date, 1), '/0', '/'), '//', '')

-- This produces what I want (7/4/09), but is verbose.
PRINT CAST(DATEPART(month, @Date) AS varchar(2)) + '/' + CAST(DATEPART(day, @Date) AS varchar(2)) + '/' + RIGHT(CAST(DATEPART(year, @Date) AS varchar(4)), 2)

That last bit of code is the only one that produces the exact format that I want, but it's verbose, and I would imagine inefficient.  What improvement can I make to get 7/4/09 from that date in a way that is both concise and efficient in SQL?  Thanks.
LVL 3
StormjackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
Try this:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(8), @Date, 3)
0
Chris MConsulting - Technology ServicesCommented:
Sorry it's this one:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(10), @Date, 103)
0
David L. HansenCEOCommented:
You could take your verbose code and create a new function with it...call it what you like then whenever you want to use it just refer to your custom function.  As far as perfomance I doubt it will be much of a hit (unless you are displaying thousands of dates).

Like this:
create function myFormatDate(@Date)
-- returns a datetime value for the specified year, month and day
returns datetime
as
    begin
    return CAST(DATEPART(month, @Date) AS varchar(2)) + '/' + CAST(DATEPART(day, @Date) AS varchar(2)) + '/' + RIGHT(CAST(DATEPART(year, @Date) AS varchar(4)), 2)

    end
go

Also see:
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/


0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

totolinoCommented:
This one does the same as your code, but is a little less verbose:

declare @my_date as datetime
set @my_date = getdate()

select CASE
-- If month between october and december, just convert in mm/dd/yyyy string
WHEN (month(@my_date) >= 10) THEN
      convert(varchar,@my_date,101)
ELSE
-- If month before october, convert and remove first char      
      substring(convert(varchar,@my_date,101),2,len(convert(varchar,@my_date,101))-1)
END
0
StormjackAuthor Commented:
Thanks pastorchris, but those dates display leading zeros.

I had considered a UDF sl8rz to make the calling stored procedure code look a little cleaner, but the underlying code still seems more verbose than I want.  Thanks for the SQL date link; it contains a lot of handy code.

It just seems there must be some other way to do this.
0
Chris MConsulting - Technology ServicesCommented:
Hi,
For no leading zeroes on both day and month, here you go:

DECLARE @Date datetime
set @Date = getdate()

select
    convert(varchar(2),day(@Date))+'/'+
    convert(varchar(2), month(@Date))+'/'+
    convert(varchar(4),year(@Date))
0
Chris MConsulting - Technology ServicesCommented:
Unfortunately totolino's post displays the leading zero in a month, so my last post passes  your requirement :-)
0
Chris MConsulting - Technology ServicesCommented:
The logic in my last post is that it gets any date parameter assigned then strips off the leading zeroes from both the date and month. Viola!! You're good to go.


Check this:

DECLARE @Date datetime
set @Date = '07/04/2010' --getdate()

select
    convert(varchar(2),day(@Date))+'/'+
    convert(varchar(2), month(@Date))+'/'+
    convert(varchar(4),year(@Date))


Result:
4/7/2010



0
Chris MConsulting - Technology ServicesCommented:
If you want to use the print function, heres' the code:


DECLARE @Date datetime
SET @Date = '07/04/2010' --getdate()

PRINT
    convert(varchar(2), day(@Date))+'/'+
    convert(varchar(2),  month(@Date))+'/'+
    convert(varchar(4), year(@Date))


Result:
4/7/2010
0
Chris MConsulting - Technology ServicesCommented:
Sorry i had not realised that you wanted the MM/DD/YYYY format!
Here's the code:


DECLARE @Date datetime
SET @Date = getdate()

PRINT
    convert(varchar(2),  month(@Date))+'/'+
    convert(varchar(2), day(@Date))+'/'+
    convert(varchar(4), year(@Date))
0
StormjackAuthor Commented:
Thanks pastorchris.
To get the 2 digit year (09 instead of 2009) a Right() method would need to be applied to the year, like so.

DECLARE @Date datetime
SET @Date = '7/4/2009'

PRINT
   convert(varchar(2),  month(@Date))+'/'+
   convert(varchar(2), day(@Date))+'/'+
   RIGHT(convert(varchar(4), year(@Date)), 2)

This would produce 7/4/09 which is what I'm after.

You're right in that Convert() with the Month(), Day(), Year() functions is less verbose.  I still dislike having to perform the string concatenation and Right() manipulation.  For now I'm going to hold out for any alternate shorter solutions than this one, though this is the strongest contender.

Thanks for all the feedback everyone, and we'll see if anyone comes up with an even better alternative.
0
Chris MConsulting - Technology ServicesCommented:
The shortest format should have been like this:
SELECT CONVERT(CHAR(8), @date, 3)

But with this, you do not eliminate the trailing zeroes!

Here's a variance of what I provided which also contends for the best:

DECLARE @Date datetime
set @Date = '07/04/2010' --getdate()

print
    convert(varchar(2),day(@Date))+'/'+
   convert(varchar(2), month(@Date))+'/'+
   substring(convert(varchar(4),year(@Date)),2,2)


Here's the output:
------------------------
7/4/01
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StormjackAuthor Commented:
Thanks for all your help pastorchris.  At present this may be the most concise solution.  If someone thinks of a better one later, please post it here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.