Select Statement problem with Datetime 1

[dpwsmw]

here is the select statement the problem is I need a 2 digit code for Month, so if the number is 5 I need it to be 05 and if the number is 9 I need it to be 09 and so on, I cant seem to figure this out. its the 15th line of code and part of the 17th and 18th

Can someone give me a hand?
select
dbo.arcv.*,
dbo.tblArCust.CustName AS Name,
dbo.tblArCust.Addr1 AS addr1,
dbo.tblArCust.Addr2 AS addr2,
dbo.tblArCust.City AS city,
dbo.tblArCust.Region AS state,
dbo.tblArCust.PostalCode AS zip,
dbo.tblArCust.SalesRepId1 AS slsrep1,
dbo.tblArCust.SalesRepId2 AS slsrep2,
dbo.tblArCust.TermsCode AS terms,
dbo.tblArCust.DistCode AS distcode,
dbo.tblArCust.CurrencyId AS currency,
dbo.tblArCust.TaxLocId AS taxloc,
dbo.tblArCust.Taxable AS taxable,
dbo.tblArCust.Rep1PctInvc AS rep1pct,
RIGHT(MONTH(dbo.arcv.InvoiceDate), 2) AS MO,
RIGHT(YEAR(dbo.arcv.InvoiceDate), 2) AS YR,
CAST(dbo.tblArCust.CustId AS char(9)) + RIGHT(MONTH(dbo.arcv.InvoiceDate), 2) + RIGHT(YEAR(dbo.arcv.InvoiceDate), 2) AS NINVC

FROM
dbo.arcv INNER JOIN
dbo.tblArCust ON dbo.arcv.SubscriberNumber = dbo.tblArCust.CustId

 

[DotNetGnat]

how about this:

RIGHT('00'+MONTH(dbo.arcv.InvoiceDate), 2)

-DNG

 

[dpwsmw]

No, that didnt work, It still truncated it to a 7 instead of a 07, Maybe I could do a case, but not sure how to write it in this..

SELECT
dbo.arcv.*,
dbo.tblArCust.CustName AS Name,
dbo.tblArCust.Addr1 AS addr1,
dbo.tblArCust.Addr2 AS addr2,
dbo.tblArCust.City AS city,
dbo.tblArCust.Region AS state,
dbo.tblArCust.PostalCode AS zip,
dbo.tblArCust.SalesRepId1 AS slsrep1, dbo.tblArCust.SalesRepId2 AS slsrep2,
dbo.tblArCust.TermsCode AS terms,
dbo.tblArCust.DistCode AS distcode, dbo.tblArCust.CurrencyId AS currency, dbo.tblArCust.TaxLocId AS taxloc,
dbo.tblArCust.Taxable AS taxable, dbo.tblArCust.Rep1PctInvc AS rep1pct,
RIGHT('00'+MONTH(dbo.arcv.InvoiceDate), 2) AS MO,
RIGHT(YEAR(dbo.arcv.InvoiceDate), 2) AS YR,
CAST(dbo.tblArCust.CustId AS char(9)) + RIGHT(MONTH(dbo.arcv.InvoiceDate), 2) + RIGHT(YEAR(dbo.arcv.InvoiceDate), 2) AS NINVC

FROM
dbo.arcv INNER JOIN
dbo.tblArCust ON dbo.arcv.SubscriberNumber = dbo.tblArCust.CustId

 

[DotNetGnat]

how about using...

DatePart(mm,dbo.arcv.InvoiceDate) AS MO

-DNG

 

[amrita418]

using

right('0' + convert(varchar, month(dbo.arcv.InvoiceDate)), 2)

OR

right('0' + convert(varchar, datepart(mm, dbo.arcv.InvoiceDate)), 2)

should do it.

Regards,
AA