Solved

Strip Month and year from date column for new column

Posted on 2011-03-22
2
369 Views
Last Modified: 2012-05-11
Hi,
I need to simply create a new column in a select statement based off the current columns.
Derived column is called xNew
and needs to be made up of the following Date1.MM + Date1.YY
i am using the cast statement
Date1 = 2010/03/06
i need column Newx to = 0310
i am using the datepart function to but it is not giving me a leading 0 infront of the 3
(hope this makes sense)

select CAST(datepart(MONTH,Date1) as varchar(2)) + CAST(datepart(YYYY,Date1) as varchar(2)) as xNew from tmpTable



thanks
0
Comment
Question by:CraigLazar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
joeyw earned 125 total points
ID: 35189454
concatenate the zeros to your result and then use the right command to extract the lenght you want.

select RIGHT('0000' + CAST(datepart(MONTH,Date1) as varchar(2)) + CAST(datepart(YYYY,Date1) as varchar(2)),4) as xNew from tmpTable



0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 35189775
perfect thanks man
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

726 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