Search code examples
sqlsql-servergetdatepatindex

Date calculation using Getdate() and patindex

Hopefully this is a simple one

  1. I want to use getdate() to tell me todays date (Done) ,
  2. then i want to take the numbers portion from a column (Don)
  3. Then I want to add 1 and 2 and generate a column damed "Date Expires" . for example if the aplhanumeric column is called CalendarHeaders.Description i will use Patindex to get the number portion , but my question is how do i add the patindex result to getdate() so that i can get the actuall expirations date?

Sample Data

CalendarHeaders.Description 
2 Days from today
5 Days from today
10 Days from today

Sample Query so far

SELECT      
  left(CalendarHeaders.Description, patindex('%[^0-9]%',  CalendarHeaders.Description+'.') - 1) as Expiration, 
  GETDATE()as DateSold

Sample Results (Missing DateExpires) 

Expiration          Datesold                    DateExpires
2                   2012-07-17 04:26:10.283      2012-07-19 04:26:10.283
5                   2012-07-17 04:26:10.283      2012-07-22 04:26:10.283
10                  2012-07-17 04:26:10.283      2012-07-27 04:26:10.283
Solution

  • You can DATEADD the parsed days to todays date;

    dateadd(DAY, cast(left(CalendarHeaders.Description, patindex('%[^0-9]%',  CalendarHeaders.Description+'.') - 1) as int), getdate()) AS DateExpires