How to present Date in SQL without leading zeros

Question

Is there a SQL format to remove leading zeros from the date?

Like if the date is 01/12/2015 to present it as 1/12/2015, and 01/01/2016 should be shown as 1/1/2016 etc

The entire date normally contains dd/MM/yyyy HH:mm:ss. I need to remove those redundant leading zeroes without changing the rest of information.

Currently I use query containing something like this:

convert(varchar, dateadd(hh, " + 2 + " , o.start_time), 103)) + ' '  
left(convert(varchar, dateadd(hh, " + 2 + " , o.start_time), 108), 110)  

I'm working with SQL Server 2008

Monocle

What data type are you using to store that date? – Arthromere

if you are using this data in an application or report, it is better to do this formatting on the front-end and keep the data in its native format when reading from the database. As soon as you format it on the way out, it is just character data and can no longer be sorted or filtered as a date. – Kalgoorlie

Have you tried "FORMAT" function... tried on sqlfiddle and this example worked and removed leading zeroes - " SELECT FORMAT ( dateadd(dd, -225, getdate()), 'd/m/y', 'en-US' )" – Kile

I'm retrieving this data from DB to some local hashMap to compare with data retrieved from the web page. The web page presents the data in shortened way, the DB with pre-coming zeros. I prefer converting the Date after quiring the data from DB to hashMap and not to convert data received from the web page to dd/MM/yyyy format – Monocle

@vmachan: FORMAT is only available in SQL Server 2012 as a new feature - not available in SQL Server 2008 which is used here – Byproduct

Answer 1

Not sure why you want to do this. Here is one way.

• Use DAY and MONTH inbuilt date functions to extract day and month from date.
• Both the function's return type is INT which will remove the unwanted leading zero
• Then concatenate the values back to form the date

Try something like this

declare @date datetime = '01/01/2016'

select cast(day(@date) as varchar(2))+'/'+cast(month(@date) as varchar(2))+'/'+cast(year(@date) as varchar(4))

Result : 1/1/2016

• DEMO

Note: Always prefer to store date in date datatype

Daynadays

Answer 2

Try this, format is a much cleaner solution:

declare @date datetime = '01/01/2016'    
SELECT FORMAT(@date,'M/d/yyyy')

result: 1/1/2016

• DEMO

Morman

Please note that this solution only words for SQL Server 2012 and later. https://mcmap.net/q/980710/-format-function-not-working-in-sql-server-2008-r2 – Hwahwan

Answer 3

One way is to use datename() for the day and year:

select cast(month(o.start_time) as varchar(255)) + '/' + datename(day, o.start_time) + '/' + datename(year, o.start_time)

An alternative method uses replace():

select replace(replace(replace('@month/@day/@year', '@month', month(o.start_time)
                              ), '@day', day(o.start_time)
                      ), '@year', year(o.start_time)
              )

Personally, I see no use for this. I always put days in YYYY-MM-DD format.

Yard