Leading Zero for DATEPART function

[TomR100]

Hello,
I am trying to get a leading zero for the DatePart function for the month. Here is the code:
SELECT CONVERT(CHAR(9),
'C' +
CONVERT(CHAR(4),DATEPART(YYYY,GETDATE())) +
CONVERT(CHAR(2),DATEPART(MM,GETDATE())) +
'15' ) AS ContractorHeader

Output: C20031 15

I would like to get a zero between the 3 and 1 but I am not sure how this would be done.

Thank you again,
TomR100

 

[JamesLean]

This should give you what you need:

SELECT 'C' + CONVERT(char(4), YEAR(getdate())) + RIGHT('00' + CONVERT(varchar(2), MONTH(getdate())), 2) + '15' AS ContractorHeader

 

[swampBoogie]

right('0'+ CONVERT(varCHAR(2),DATEPART(MM,GETDATE())),2) +

 

[dinzana]

Try this,


SELECT CONVERT(CHAR(9),
'C' +
CONVERT(varCHAR(4),DATEPART(YYYY,GETDATE())) +
CONVERT (varchar(2), GETDATE(), 101) +
'15' ) AS ContractorHeader

 

[AWithers]

and another

SELECT CONVERT(CHAR(9),
'C' +
CONVERT(CHAR(4),DATEPART(YYYY,GETDATE())) +
select substring(convert(varchar,GETDATE(),1),1,2)+
'15' ) AS ContractorHeader

Andy

 

[TomR100]

Thank you ALL for your help.
Hey here is another one that might work

SELECT CONVERT
(CHAR(9),
'C' +
CONVERT(CHAR(4),DATEPART(YYYY,GETDATE())) +
CASE when DATEPART(MM,GETDATE()) < '10' then
('0' + CONVERT(CHAR(1),DATEPART(MM,GETDATE())))
else CONVERT(CHAR(2),DATEPART(MM,GETDATE()))end +
'15' ) AS ContractorHeader

What do you think?

 

[JamesLean]

It would certainly work, but it's not quite as concise as some of the others (don't use 10 lines when 1 will do!)

Also you can use the MONTH() and YEAR() functions as per my original post. Again, just for neatness really.

 

[MeanGreen]

Here is another method of getting what you want. The style 112 will prefix a zero for the months.

SELECT 'C' +
convert(char(6),getdate(),112)+
'15' AS ContractorHeader

Hope this helps.