php - How to convert SCORM 2004 session_time format to a usable datetime format in SQL -

so scorm 2004's session_time format looks following

p[yy][mm][dd][t[hh][mm][s[.s]s]] 

where options in square brackets optional, must include @ least 1 of options.

an example of format pt0h0m47s

which means 0 hours, 0 minutes , 47 seconds.

the value stored varchar in mssql database.

i need able convert string format usable format can add time onto start date time work out end date time.

i moving scorm data old database new 1. in php , format time prefer write of import scripts in sql.

any on appreciated.

edit:

if helps, php function wrote handle format purpose

private function formatduration($duration) {     $count = preg_match('/p(([0-9]+)y)?(([0-9]+)m)?(([0-9]+)d)?t?(([0-9]+)h)?(([0-9]+)m)?(([0-9]+)(\.[0-9]+)?s)?/', $duration, $matches);      if ($count)     {         $_years = (int) $matches[2];         $_months = (int) $matches[4];         $_days = (int) $matches[6];         $_hours = (int) $matches[8];         $_minutes = (int) $matches[10];         $_seconds = (int) $matches[12];     }     else     {         if (strstr($duration, ':'))         {             list($_hours, $_minutes, $_seconds) = explode(':', $duration);         }         else         {             $_hours = 0;             $_minutes = 0;             $_seconds = 0;         }     }      // ignore years, months , days unlikely     // course take longer 1 hour     return $_seconds + (($_minutes + ($_hours * 60)) * 60); } 

just worked out solution me.

this need put inside sql function me , need finish off last bit decide format want.

i updating around 900 rows , 1 off update performance isn't issue.

declare @session_time varchar(200) = 'p50y2m3dt4h5m6s'  declare @date varchar(100) = '' declare @time varchar(100) = ''  declare @year varchar(20) = '0' declare @month varchar(20) = '0' declare @day varchar(20) = '0' declare @hour varchar(20) = '0' declare @minute varchar(20) = '0' declare @second varchar(20) = '0'  -- remove p set @session_time = substring(@session_time, 2, len(@session_time)-1)  -- if have t if patindex('%t%',@session_time) > 0 begin     set @date = substring(@session_time, 0, patindex('%t%',@session_time))      set @time = substring(@session_time, len(@date + 't') + 1, len(@session_time)) end else begin     set @time = @session_time; end  -- date parts if len(@date) > 0 begin     -- if theres year     if patindex('%y%',@date) > 0     begin         set @year = substring(@date, 0, patindex('%y%',@date))          set @date = substring(@date, len(@year + 'y') + 1, len(@date))     end      -- if theres month     if patindex('%m%',@date) > 0     begin         set @month = substring(@date, 0, patindex('%m%',@date))          set @date = substring(@date, len(@month + 'm') + 1, len(@date))     end      -- if theres day     if patindex('%d%',@date) > 0     begin         set @day = substring(@date, 0, patindex('%d%',@date))          set @date = substring(@date, len(@day + 'd') + 1, len(@date))     end end  -- time parts if len(@time) > 0 begin     -- if theres hour     if patindex('%h%',@time) > 0     begin         set @hour = substring(@time, 0, patindex('%h%',@time))          set @time = substring(@time, len(@hour + 'h') + 1, len(@time))     end      -- if theres minute     if patindex('%m%',@time) > 0     begin         set @minute = substring(@time, 0, patindex('%m%',@time))          set @time = substring(@time, len(@minute + 'm') + 1, len(@time))     end      -- if theres second     if patindex('%s%',@time) > 0     begin         set @second = substring(@time, 0, patindex('%s%',@time))          set @time = substring(@time, len(@second + 's') + 1, len(@time))     end end  print @year + ' years ' print @month + ' months ' print @day + ' days ' print @hour + ' hours ' print @minute + ' minutes ' print @second + ' seconds '