t-sql DATEPART TO RETURN 2 DIGIT MONTH

Hi Experts

I've succeded in creating a concatenated field which creates a reference but where I would like the last four digits to be the date and month, my DATEPART only returns the single digit where the date or month are less than 10.  This creates uncertainty where the field states 311, etc - is it 3 Nov or 31 Jan.  Is there a way to make DATEPART return two digits?  Code below for clarity.

UPDATE  dbo.Sales
SET         dbo.Sales.[Sales Checkfile] =
               CAST([Branch Number] AS VARCHAR(4)) +
               CAST([Delivery Number] AS VARCHAR(6)) +
               CAST([Delivery Item Number] AS VARCHAR(3))+
               CAST(DATEPART(dd, [Actual Posting Date]) AS VARCHAR(2)) +
               CAST(DATEPART(mm, [Actual Posting Date]) AS VARCHAR(2))

Many thanks.

Karyn
elainesisterAsked:
Who is Participating?
 
deightonprogCommented:
UPDATE  dbo.Sales
SET         dbo.Sales.[Sales Checkfile] =
               CAST([Branch Number] AS VARCHAR(4)) +
               CAST([Delivery Number] AS VARCHAR(6)) +
               CAST([Delivery Item Number] AS VARCHAR(3))+
               RIGHT('00' + CAST(DATEPART(dd, [Actual Posting Date]) AS VARCHAR(2)) ,2) +
               RIGHT('00' + CAST(DATEPART(mm, [Actual Posting Date]) AS VARCHAR(2))  , 2)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can make it like this:
UPDATE  dbo.Sales
SET         dbo.Sales.[Sales Checkfile] =
               CAST([Branch Number] AS VARCHAR(4)) +
               CAST([Delivery Number] AS VARCHAR(6)) +
               CAST([Delivery Item Number] AS VARCHAR(3))+
               CONVERT(VARCHAR(4), [Actual Posting Date], 12)

Open in new window


http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
elainesisterAuthor Commented:
Many thanks, deighton - worked like a dream!

Karyn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and mine didn't work?
0
 
elainesisterAuthor Commented:
Sorry, angelIII - I couldn't see how to post on your comment - I picked up deighton's first and just went with it.  I'm sure yours would work also and I'm grateful for your input.

Many thanks.

Karyn
0
 
deightonprogCommented:
ANGELIII  - on my system, if I do

select CONVERT(VARCHAR(4), getdate(), 12)

I get

1209

when it would be 0409 which is what would be wanted, so I'm not sure if it always works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.