CODEWITHSUNDEEP
sqlsql-serversql-server-2008date-formatdate-formatting
Prophet
Prophet

Reputation: 33361

How to present Date in SQL without leading zeros

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

Upvotes: 5

Views: 23775

Answers (3)

Matt
Matt

Reputation: 872

Try this, format is a much cleaner solution:

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

result: 1/1/2016

Upvotes: 14

Gordon Linoff
Gordon Linoff

Reputation: 1270371

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.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

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

Note: Always prefer to store date in date datatype

Upvotes: 4

Related Questions