?
Solved

Strip Month and year from date column for new column

Posted on 2011-03-22
2
Medium Priority
?
372 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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