Returning a Date with 0 in front of the Month and Day

Question

Returning a Date with 0 in front of the Month and Day

HookSQLDBA7

SSChampion

Points: 10709
More actions
September 15, 2004 at 1:55 pm

#61147

I would like to return the current date in SQL and have it return a zero in front of the month and day if they are 1 thru 9? For example, 09/08/2004. I also would like to format this date as CCYYMMDD using only SQL Code.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

There are lots of ways to do this, and somebody might chime in with a better idea (I'm not convinced this is the best way to go, but I'm a little dazed from work right now), but here's one way to at least get you started:

declare @date datetime
declare @date_CCYYMMDD char(8)
set @date = convert(datetime, '1/2/2003 4:05 AM')

print right('0000' + cast(datepart (Year, @date) as varchar), 4) + 
      right('00' + cast(datepart (Month, @date) as varchar), 2) +
      right('00' + cast(datepart (Day, @date) as varchar), 2)

Here's a similar example, using a SELECT statement to print the current date in CCYYMMDD format:

select right('0000' + cast(datepart (Year, getdate()) as varchar), 4) + 
      right('00' + cast(datepart (Month, getdate()) as varchar), 2) +
      right('00' + cast(datepart (Day, getdate()) as varchar), 2)

You might consider bottling this functionality up in a user-defined function (UDF), but as always you should be careful about performance with UDFs.

Good luck,

Chris

adrian_rf SSC Veteran Points: 275 More actions · Answer 2

Lookup CONVERT in BOL.

SELECT CONVERT (varchar(10), GetDate(), 103) ...16/09/2004

SELECT CONVERT (varchar(10), GetDate(), 112) ...20040916

chrisleonard SSCertifiable Points: 5030 More actions · Answer 3

Adrian ... thanks. Gack, I can't believe I missed that the format Kevin wanted was one of the CONVERT formats. Grrrr to me for being in a rush ... but thanks for correcting my post.

At least it's been a little while ... I think ... since I posted something so stupid.

-Chris