11

Right now, my code has the following conversion for a date field:

convert(varchar, Citation.PublishedOn, 101)

However, that returns dates like 03/01/2010. The request was to have the dates display as 3/1/2010 (without the leading zeros, but with a 4 digit year). I've looked at http://msdn.microsoft.com/en-us/library/ms187928.aspx and I'm not seeing anything that explicitly excludes leading zeros.

How do I format the date to exclude leading zeros?

thursdaysgeek
  • 7,260
  • 18
  • 72
  • 104

4 Answers4

16

This is how I would do it:

DECLARE @dt datetime
SET @dt= Citation.PublishedOn
SELECT LTRIM(STR(MONTH(@dt)))+'/'+LTRIM(STR(DAY(@dt)))+'/'+STR(YEAR(@dt),4)

You select your date, then extract the day, month and year from it and chop the leading zeroes off the month and day using ltrim().

If you don't want to declare a variable, you can do this 

SELECT LTRIM(STR(MONTH(Citation.PublishedOn)))+'/'+LTRIM(STR(DAY(Citation.PublishedOn)))+'/'+STR(YEAR(Citation.PublishedOn),4)

However, that would mean pulling out the same value multiple times.

Mark Costello
  • 4,094
  • 3
  • 20
  • 23
  • Bingo! However, after converting to M/D/YYYY, the problem is Order By Date query is giving result like 4/1/2014, 4/10/2014, 4/7/2014 when it supposed to be 4/1/2014, 4/7/2014, 4/10/2014. Is there any way to achieve this keeping M/D/YYYY format intact? Please suggest. Thank you! – Subrata Sarkar May 02 '14 at 07:38
  • 1
    @NiladriSarkar, I think you'll need to ORDER BY a different column – Darren Griffith May 15 '14 at 22:46
  • 1
    Similarly, SELECT CAST(MONTH(@dt) as varchar) +'/'+ CAST(DAY(@dt) as varchar) +'/'+ CAST(YEAR(@dt) as varchar) – Darren Griffith May 15 '14 at 22:47
  • @SubrataSarkar I would pull the real date field into a sepparate field (ex _date_sort) in your view, hide it from the user in any display, and sort on that, or sort on the date column in your view. Right now the sort is sorting based on string sort rules, so it's comparing each character rather than the whole value since it is now a string type. – DavidScherer Feb 15 '19 at 21:09
  • Best solution for cobbling together a date string I've found so far in terms of maintainability, flexibility, and interpretability for future devs (because in my experience, if you're on 2008, you're probably gonna be there for a while, seems like M$ shops take the longest to upgrade things) – DavidScherer Feb 15 '19 at 21:15
6

You can use the FORMAT function, which is built for just this sort of thing, although I agree that if you can wait to format it on the client side you may be better off as you give yourself the flexibility to use that data as a date at any point along the line (ymmv - just more of a best practice).

Example:

FORMAT ( table.myDateColumn, 'd', 'en-US' )

See http://msdn.microsoft.com/en-us/library/hh213505.aspx

May not be available in older versions of SQL Server

jinglesthula
  • 4,162
  • 4
  • 43
  • 72
  • Was that available in SQL Server 2008 too? If so, nice - I wish I had found that. If not, then it didn't answer the question as asked, but can still be useful for others who do have SQL Server 2012. – thursdaysgeek Dec 14 '12 at 00:58
  • 1
    No it doesn't. It will give error message 'FORMAT' is not a recognized built-in function name. – Subrata Sarkar May 02 '14 at 07:41
  • Is this the preferred method SQL 2012 onward? – Heckflosse_230 Dec 12 '14 at 18:02
  • @Heckflosse_230 it certainly avoids clever tricks that are more challenging to read/maintain/debug, and is a built-in and concise way of handling it – jinglesthula Jul 09 '15 at 18:23
  • Unfortunately it it took M$ 23 years or so to figure out that some use cases require the ability to FORMAT() date strings and then implement it. So if you're stuck on anything prior to SQL Server 2012 you're left to your own devices to cobble together a formatted date string. – DavidScherer Feb 15 '19 at 21:12
3

You could do:

STUFF(REPLACE('/'+CONVERT(CHAR(10), Citation.PublishedOn ,101),'/0','/'),1,1,'')

(Based on http://www.sqlservercentral.com/Forums/Topic1241877-1292-1.aspx.)

yoel halb
  • 10,405
  • 3
  • 48
  • 46
2

I took a different approach, which is more of a trick:

REPLACE(REPLACE('a'+ @table.date ),'a0',''),'a','')

I thought it was spiffy, but I got the idea after interpreting the STUFF trick above incorrectly. :)

Tisho
  • 7,474
  • 5
  • 40
  • 52
Mikhail Nitko
  • 21
  • 1
  • 1
    as a side note-- since most folks who want to see singular month dates, want to see them at the head of the entire date field-- I assumed you already have your date in 101 format. ie: 08/01/12 – Mikhail Nitko Aug 01 '12 at 15:06
  • also, keep in mind, if youre trimming from a larger date representation, with a "char(10)" style, you will need to replace the occurances of 'a0' and 'a' with blank spaces ala:--> ,'a0',' ') and not deletes ala:--> ,'a0','') – Mikhail Nitko Aug 01 '12 at 15:11