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