[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-04-08
13
Medium Priority
?
3,611 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Stormjack
13 Comments
 
LVL 12

Expert Comment

by:Chris M
ID: 30114314
Try this:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(8), @Date, 3)
0
 
LVL 12

Expert Comment

by:Chris M
ID: 30114371
Sorry it's this one:
DECLARE @Date datetime
SET @Date = getdate()

SELECT CONVERT(CHAR(10), @Date, 103)
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 30114595
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Expert Comment

by:totolino
ID: 30115412
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
 
LVL 3

Author Comment

by:Stormjack
ID: 30115441
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
 
LVL 12

Expert Comment

by:Chris M
ID: 30115983
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
 
LVL 12

Expert Comment

by:Chris M
ID: 30116091
Unfortunately totolino's post displays the leading zero in a month, so my last post passes  your requirement :-)
0
 
LVL 12

Expert Comment

by:Chris M
ID: 30116321
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
 
LVL 12

Expert Comment

by:Chris M
ID: 30116627
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
 
LVL 12

Expert Comment

by:Chris M
ID: 30116761
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
 
LVL 3

Author Comment

by:Stormjack
ID: 30122868
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
 
LVL 12

Accepted Solution

by:
Chris M earned 2000 total points
ID: 30205990
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
 
LVL 3

Author Closing Comment

by:Stormjack
ID: 31712285
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question