So SCORM 2004's session_time format looks like the following
P[yY][mM][dD][T[hH][mM][s[.s]S]]
Where the options in the square brackets are optional, however it must include at least 1 of the options.
An example of the format is PT0H0M47S
Which means 0 hours, 0 minutes and 47 seconds.
The value is stored as varchar in a MSSQL database.
I need to be able to convert this string format into a usable format so that I can add this time onto a Start Date Time to work out the End Date Time.
I am currently moving the SCORM data from an old database to a new 1. I could do this in PHP and format the time easily but I would prefer to write all of the import scripts in SQL.
Any help on this would be greatly appreciated.
EDIT:
If it helps, this is a PHP function I wrote to handle the format for another 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;
}
}
// I just ignore years, months and days as it is unlikely that a
// course would take any longer than 1 hour
return $_seconds + (($_minutes + ($_hours * 60)) * 60);
}
Just worked out a solution for me.
This would need to be put inside a SQL function for me and I would need to finish off the last bit where I decide what format I want.
I am only updating around 900 rows and it is a 1 off update so performance isn't an 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 the P
SET @session_time = SUBSTRING(@session_time, 2, LEN(@session_time)-1)
-- If we have a 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
-- Get the date parts
IF LEN(@date) > 0
BEGIN
-- If theres a 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 a 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 a 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
-- Get the time parts
IF LEN(@time) > 0
BEGIN
-- If theres an 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 a 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 a 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 '