TSQL - converting date string to datetime

Question

I have a table that has the following column (with some sample entries provided):

COLUMN NAME: pt_age_old

20 Years 8 Months 3 Weeks
1 Year 3 Months 2 Weeks
58 Years 7 Months
1 Year
7 Years 11 Months 2 Weeks
26 Years 6 Months
56 Years 6 Months
48 Years 6 Months 4 Weeks
29 Years 11 Months
4 Years 3 Months
61 Years 8 Months 4 Weeks

I have tried to cast it to datetime, of course this did not work. Same with convert. Keep getting the following message:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Main 2 questions are:

Is this type of conversion even possible with this existing string format?

If so, can you steer me in the right direction so I can make this happen?

Thanks!

What would you 61 Years 8 Months 4 Weeks expect to be as datetime? It is not even clear how many days a year or a month has. What is the "zero-time" at all? Should the timespan be subtracted from or added to this datetime? — Tim Schmelter
– Tim Schmelter, Commented Oct 16, 2013 at 21:08
no. it's not possible. those aren't dates. they're DURATIONS or time periods. you'd need to know what the "base time" (aka epoch) is, so you can so WHAT those dates are relative to. e.g. jan 1 1980 + 20 years + 8 months + 3 weeks = your actual date you'd want to save in the db. — Marc B
– Marc B, Commented Oct 16, 2013 at 21:09
With some tweaking it is possible - but to convert the above to date you need to express how it should become a date - i.e. it's 20 years, 8 months and 3 weeks since/until what? — JohnLBevan
– JohnLBevan, Commented Oct 16, 2013 at 21:09
It's an age or something, what are you trying to actually do with it? — Andrew
– Andrew, Commented Oct 16, 2013 at 21:09
Marc B, they could be construed as time period. But in this context, they are age. As in a person is 61 years 8 months 4 weeks old. — George
– George, Commented Oct 17, 2013 at 17:18

JohnLBevan · Accepted Answer · 2013-10-16 21:43:56Z

This could be done using custom code such as below - I've assumed the values you're using are people's ages, and you're trying to work out their date of birth given their age today.

You can see the below code in action here: http://sqlfiddle.com/#!3/c757c/2

create function dbo.AgeToDOB(@age nvarchar(32))
returns datetime
as
begin

    declare @pointer int = 0
    , @pointerPrev int = 1
    , @y nvarchar(6)
    , @m nvarchar(6)
    , @w nvarchar(6)
    , @d nvarchar(6)
    , @result datetime = getutcdate() --set this to the date we're working to/from

    --convert various ways of expressing units to a standard
    set @age = REPLACE(@age,'Years','Y')
    set @age = REPLACE(@age,'Year','Y')
    set @age = REPLACE(@age,'Months','M')
    set @age = REPLACE(@age,'Month','M')
    set @age = REPLACE(@age,'Weeks','W')
    set @age = REPLACE(@age,'Week','W')
    set @age = REPLACE(@age,'Days','D')
    set @age = REPLACE(@age,'Day','D')

    set @pointer = isnull(nullif(CHARINDEX('Y',@age),0),@pointer)
    set @y = case when @pointer > @pointerprev then SUBSTRING(@age,@pointerprev,@pointer - @pointerprev) else null end

    set @pointerPrev = @pointer + 1
    set @pointer = isnull(nullif(CHARINDEX('M',@age),0),@pointer)
    set @m = case when @pointer > @pointerprev then SUBSTRING(@age,@pointerprev,@pointer - @pointerprev) else null end

    set @pointerPrev = @pointer + 1
    set @pointer = isnull(nullif(CHARINDEX('W',@age),0),@pointer)
    set @w = case when @pointer > @pointerprev then SUBSTRING(@age,@pointerprev,@pointer - @pointerprev) else null end

    set @pointerPrev = @pointer + 1
    set @pointer = isnull(nullif(CHARINDEX('D',@age),0),@pointer)
    set @d = case when @pointer > @pointerprev then SUBSTRING(@age,@pointerprev,@pointer - @pointerprev) else null end

    set @result =  dateadd(YEAR, 0 - isnull(cast(@y as int),0), @result)
    set @result =  dateadd(MONTH, 0 - isnull(cast(@m as int),0), @result)
    set @result =  dateadd(Week, 0 - isnull(cast(@w as int),0), @result)
    set @result =  dateadd(Day, 0 - isnull(cast(@d as int),0), @result)

    return @result

end

go

select dbo.AgeToDOB( '20 Years 8 Months 3 Weeks')

NB: there's a lot of scope for optimisation here; I've left it simple above to help keep it clear what's going on.

This seems very feasable! I'm going to give this a try. Thank you for the help John!

Ryan · Accepted Answer · 2013-10-17 02:19:12Z

Technically it is possible to convert the relative time label into a datetime, but you will need a reference date though (20 Years 8 Months 3 Weeks as of '2013-10-16'). The reference date is most likely today (using GETDATE() or CURRENT_TIMESTAMP), but there is a chance it is a different date. You will have to parse the label, convert it to a duration, and then apply the duration to the reference time. This will be inherently slow.

There are at least two possible ways of doing this, write a FUNCTION to parse and convert the relative time label or use a .NET extended function to do the conversion. You would need to identify all of the possible labels otherwise the conversion will fail. Keep in mind that the reference date is important since 2 months is not a constant number of days (Jan-Feb = either 58 or 59 days).

Here is a sample of what the function might look like:

-- Test data
DECLARE @test varchar(50)
      , @ref_date datetime

SET @test = '20 Years 8 Months 3 Weeks'
SET @ref_date = '2013-10-16' -- or use GETDATE() / CURRENT_TIMESTAMP

-- Logic in function
DECLARE @pos int
      , @ii int
      , @val int
      , @label varchar(50)
      , @result datetime

SET @pos = 0
SET @result = @ref_date

WHILE (@pos <= LEN(@test))
BEGIN
  -- Parse the value first
  SET @ii = NULLIF(CHARINDEX(' ', @test, @pos), 0)
  SET @label = RTRIM(LTRIM(SUBSTRING(@test, @pos, @ii - @pos)))
  SET @val = CAST(@label AS int)
  SET @pos = @ii + 1

  -- Parse the label next
  SET @ii = NULLIF(CHARINDEX(' ', @test, @pos), 0)
  IF (@ii IS NULL) SET @ii = LEN(@test) + 1
  SET @label = RTRIM(LTRIM(SUBSTRING(@test, @pos, @ii - @pos)))
  SET @pos = @ii + 1

  -- Apply the value and offset
  IF (@label = 'Days') OR (@label = 'Day')
    SET @result = DATEADD(dd, -@val, @result)
  ELSE IF (@label = 'Weeks') OR (@label = 'Week')
    SET @result = DATEADD(ww, -@val, @result)
  ELSE IF (@label = 'Months') OR (@label = 'Month')
    SET @result = DATEADD(mm, -@val, @result)
  ELSE IF (@label = 'Years') OR (@label = 'Year')
    SET @result = DATEADD(yy, -@val, @result)

END

SELECT @result
-- So if this works correctly,
-- 20 Years 8 Months 3 Weeks from 2013-10-16 == 1993-01-26

t-clausen.dk · Accepted Answer · 2013-10-17 09:01:16Z

Make a function to split it up:

create function f_tst
(
@t varchar(200)
) returns date
begin
declare @a date = current_timestamp
;with split1 as
(
  select 1 start, charindex(' ', @t + ' ', 4)+1 stop
  where @t like '% %'
  union all
  select stop, charindex(' ', @t + ' ', stop + 4)+1 
  from split1
  where charindex(' ', @t, stop) > 0
), split2 as
(
  select cast(left(x.sub, charindex(' ', x.sub)) as int) number, 
  substring(x.sub, charindex(' ', x.sub) + 1, 1) unit 
  from split1
  cross apply (select substring(@t, start, stop - start) sub) x
)
select @a = case unit when 'W' then dateadd(ww, -number, @a)
                       when 'Y' then dateadd(yy, -number, @a)
                       when 'M' then dateadd(mm, -number, @a)
            end
from split2
return @a
end

Test function:

select dbo.f_tst(age)
from (values('20 Years 8 Months 3 Weeks'),('1 Month') ) x(age)

Result:

1993-01-27
2013-09-17

Iłya Bursov · Accepted Answer · 2013-10-16 21:08:44Z

0

No, date time type presents actual date, like YYYY-MM-DD HH:MM, your strings are not DATE fields, they are age, to have date time you need date of birth and them somehow add this age to it

answered Oct 16, 2013 at 21:08

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Collectives™ on Stack Overflow

TSQL - converting date string to datetime

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related