asked on

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

Guy Hengel [angelIII / a3]

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

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elainesister

ASKER

Many thanks, deighton - worked like a dream!

Karyn

Guy Hengel [angelIII / a3]

and mine didn't work?

elainesister

ASKER

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

deighton

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