Convert text to date.

[dweebe]

I'm a level test analyst and am trying to figure out an sql.

I have one column (called MOD_NUMBER) that has four characters. From that I need to take the last two digits and make them the year of a date string. For example if the MOD_NUMBER is 8310, the date needs to be 01-01-2010 00:00:00. If the MOD_NUMBER is 9902, the date needs to be 01-01-2002 00:00:00.


This is my SQL and I can't any way to get it to work:

select
dateadd('yy',substr(t1.MOD_NUMBER, 3, 2) as USPS_START_DATE,'01-01-2000 00:00:00')
from
M_VEHICLE_MODIFICATION t1

What am I doing wrong?

 

[r937]

assuming you're on SQL Server,

select dateadd(year
              ,substr(t1.MOD_NUMBER,3,2) 
              ,'01-01-2000 00:00:00')
           as USPS_START_DATE
  from M_VEHICLE_MODIFICATION t1

r937.com | rudy.ca