Selecting specific characters in SQL DTS?

Question

I have some date data in a stock spread sheet that reads in the 03/04/09 format (DD-MM-YY.) From this data, I want to populate a Registration year(which is set to type INT.) field in the database *only* with the year part at the end. (so for example 09.) Basically I need to know what function I would need to use in SQL to achieve this. (A brief example in the correct syntax would be appreciated.)

Answer 1

if your using SQL 2005 or above and it's a text string use the



substring(date,4,2) or if it's a date field use datepart(mm,date)



where date being the field

Answer 2

Well, if you know for certain that the string containing the date (assuming it is stored in string format) is always going to be the same length, you could use something like the RIGHT function in SQL, like this :



SELECT RIGHT('03-18-09', 2)

(from http://sqltutorials.blogspot.com/2007/06/sql-string-functions.html)



Now, if the date is stored in DATE format instead of string, you will have to do some conversion to string type. Check this site out :



http://www.mssqltips.com/tip.asp?tip=1145



Hope this helps : )

Answer 3

select datepart(yyyy,cast(DATEINPUT as smalldatetime)) as 'Year') from dataset.



Wiuth datepart you can grab any portion of a date you want.