Link to home
Start Free TrialLog in
Avatar of Stormjack
StormjackFlag for United States of America

asked on

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.
Avatar of Chris M
Chris M
Flag of Uganda image

Try this:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(8), @Date, 3)
Sorry it's this one:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(10), @Date, 103)
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/


Avatar of totolino
totolino

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
Avatar of Stormjack

ASKER

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.
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))
Unfortunately totolino's post displays the leading zero in a month, so my last post passes  your requirement :-)
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



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
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))
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.
ASKER CERTIFIED SOLUTION
Avatar of Chris M
Chris M
Flag of Uganda image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.