[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

t-sql DATEPART TO RETURN 2 DIGIT MONTH

Posted on 2012-09-03
6
Medium Priority
?
5,527 Views
Last Modified: 2012-09-04
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
0
Comment
Question by:elainesister
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38360747
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
 
LVL 18

Accepted Solution

by:
deighton earned 2000 total points
ID: 38360817
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
 

Author Closing Comment

by:elainesister
ID: 38362535
Many thanks, deighton - worked like a dream!

Karyn
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38362693
and mine didn't work?
0
 

Author Comment

by:elainesister
ID: 38362699
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
 
LVL 18

Expert Comment

by:deighton
ID: 38362781
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question