Stormjack
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.
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.
Sorry it's this one:
DECLARE @Date datetime
SET @Date = getdate()
SELECT CONVERT(CHAR(10), @Date, 103)
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/
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/
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,1 01)
ELSE
-- If month before october, convert and remove first char
substring(convert(varchar, @my_date,1 01),2,len( convert(va rchar,@my_ date,101)) -1)
END
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,1
ELSE
-- If month before october, convert and remove first char
substring(convert(varchar,
END
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.
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(@Da te))+'/'+
convert(varchar(2), month(@Date))+'/'+
convert(varchar(4),year(@D ate))
For no leading zeroes on both day and month, here you go:
DECLARE @Date datetime
set @Date = getdate()
select
convert(varchar(2),day(@Da
convert(varchar(2), month(@Date))+'/'+
convert(varchar(4),year(@D
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(@Da te))+'/'+
convert(varchar(2), month(@Date))+'/'+
convert(varchar(4),year(@D ate))
Result:
4/7/2010
Check this:
DECLARE @Date datetime
set @Date = '07/04/2010' --getdate()
select
convert(varchar(2),day(@Da
convert(varchar(2), month(@Date))+'/'+
convert(varchar(4),year(@D
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
DECLARE @Date datetime
SET @Date = '07/04/2010' --getdate()
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))
Here's the code:
DECLARE @Date datetime
SET @Date = getdate()
convert(varchar(2), month(@Date))+'/'+
convert(varchar(2), day(@Date))+'/'+
convert(varchar(4), year(@Date))
ASKER
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.
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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
DECLARE @Date datetime
SET @Date = getdate()
SELECT CONVERT(CHAR(8), @Date, 3)