How to extract dates from text string?
-
We have a CRM application used for trouble ticketing. It doesn't have a field for the start date/time of the ticket or last action. Instead I have a text field which looks like this:
Ticket created. Assigned to user davidp -- Friday 10th November 2006 12:36:41 PM by davidp--//-- -- Friday 10th November 2006 12:37:01 PM by davidp--//-- Transferred to group Support Group\. -- Monday 13th November 2006 02:06:39 PM by davidp--//-- -- Thursday 16th November 2006 10:47:40 AM by davidp--//-- Status Changed to Wait For Response\. Priority Changed to Normal\. Category Changed to Other Problem\. -- Thursday 16th November 2006 10:49:46 AM by davidp--//-- Priority Changed to 3 - Minor\. Category Changed to PC - Software\. -- Monday 04th December 2006 01:28:12 PM by davidp--//--
Or this:
Ticket created. Assigned to group Support Group -- Thursday 16th November 2006 03:31:50 PM by davidp--//-- Status Changed to Closed\. -- Thursday 16th November 2006 03:59:30 PM by davidp--//--
I want to add a calculated field to extract the first and last dates & times in the string so I can use them in my report.
Can anyone help?
-
Are the dates always surrounded by '--' If so you could look for the -- followed by a 'by' and use that as a way to get your string date. Convert your string date to a datetime and you're off...
Alternatively, if your text feild is appended to and not overwritten each time, you could add a trigger that finds the last date and writes it to your datetime on insert, update etc...
-
It does look like this is the case (although I've only been working iwth the data for a couple of days, so I can't be certain).
So more questions:
1. What expression do I use identify to return the first or last instances of the date strings within the text, especially where there are more than two like in first example?
2. How do I convert the string of say "Friday 10th November 2006 12:36:41 PM" to a date-time
-
Well the string to date would be easy enough... something like the following should work, you could perhaps make it a function or something...
SET NOCount ON
DECLARE @MyVal VARCHAR(50),
@MyDayRemove VARCHAR(50),
@DayCounter INT
SELECT @MyVal = 'FRIDAY 10th November 2006 12:36:41 PM'
SELECT @DayCounter = 0
WHILE @Daycounter < 7
BEGIN
SELECT @MyVal = REPLACE(@MyVal,DATENAME(dw,GETDATE()+ @Daycounter),'')
SELECT @DayCounter = @DayCounter +1
END
SELECT @MyVal = REPLACE(@MyVal,'th ',' ')
SELECT @MyVal = REPLACE(@MyVal,'rd ',' ')
SELECT @MyVal = REPLACE(@MyVal,'nd ',' ')
SELECT CAST(@MyVal AS datetime)
As for finding the dates... I'd need to do a bit more thinking on that...
-
Although this is most likely not the fastest code to get around something like this IT would work for the first date and you could build something like it in reverse for the last date...
DECLARE @strTest VARCHAR(4000),
@cntrTest INT,
@firstdatestart INT,
@firstdateEnd INT
SELECT @strTest = 'Ticket created. Assigned to user davidp -- Friday 10th November 2006 12:36:41 PM by davidp--//-- -- Friday 10th November 2006 12:37:01 PM by davidp--//-- Transferred to group Support Group\. -- Monday 13th November 2006 02:06:39 PM by davidp--//-- -- Thursday 16th November 2006 10:47:40 AM by davidp--//-- Status Changed to Wait For Response\. Priority Changed to Normal\. Category Changed to Other Problem\. -- Thursday 16th November 2006 10:49:46 AM by davidp--//-- Priority Changed to 3 - Minor\. Category Changed to PC - Software\. -- Monday 04th December 2006 01:28:12 PM by davidp--//--'
SELECT @cntrTest = 1, @firstdatestart = 0, @firstdateend = 0
WHILE @cntrTest < LEN(@strTest)
BEGIN
IF SUBSTRING(@strTest, @cntrTest, 2)= '--' AND @firstDateStart = 0 AND @firstdateend = 0
BEGIN
SELECT @firstDateStart = @cntrTest+2
END
IF SUBSTRING(@strTest, @cntrTest, 2) = '--' AND @firstDateStart < @cntrTest AND @firstdateend = 0
BEGIN
SELECT @firstdateend = (@cntrTest)
END
SELECT @cntrTest = @cntrTest +1
END
SELECT SUBSTRING(@strTest, 43,@firstdateend-@firstDateStart)
-
I can name that date in one query
-- Use STUFF to remove the date suffix, then cast the strings to datetime
-- If the second character is a digit remove two characters starting in column three, otherwise start in column two
SELECT FirstDate = CAST(STUFF(FirstDate, CASE WHEN SUBSTRING(FirstDate, 2, 1) LIKE '[0-9]' THEN 3 ELSE 2 END, 2, '') AS datetime),
LastDate = CAST(STUFF(LastDate, CASE WHEN SUBSTRING(LastDate, 2, 1) LIKE '[0-9]' THEN 3 ELSE 2 END, 2, '') AS datetime)
FROM (
SELECT FirstDate = SUBSTRING(TicketText, FirstDate, FirstLength),
LastDate = SUBSTRING(TicketText, LastDate, LastLength)
FROM (
-- Look for AM or PM to find the length of the date strings
SELECT TicketText, FirstDate, LastDate,
FirstLength = PATINDEX('% [AP]M %', SUBSTRING(TicketText, FirstDate, 99)) + 2,
LastLength = PATINDEX('% [AP]M %', SUBSTRING(TicketText, LastDate, 99)) + 2
FROM (
-- Use the first (or last) appearance of a day name to indicate start position
-- Skip the day name as it is not needed to get the date value
SELECT TicketText, FirstDate = MIN(CHARINDEX(dayname, TicketText) + LEN(dayname)) + 1,
LastDate = MAX(LEN(TicketText) - CHARINDEX(REVERSE(dayname), REVERSE(TicketText))) + 2
FROM (
SELECT 'Ticket created. Assigned to user davidp -- Friday 10th November 2006 12:36:41 PM by davidp--//-- ' +
'-- Friday 10th November 2006 12:37:01 PM by davidp--//-- Transferred to group Support Group\. ' +
'-- Monday 13th November 2006 02:06:39 PM by davidp--//-- -- Thursday 16th November 2006 10:47:40 AM ' +
'by davidp--//-- Status Changed to Wait For Response\. Priority Changed to Normal\. Category Changed to ' +
'Other Problem\. -- Thursday 16th November 2006 10:49:46 AM by davidp--//-- Priority Changed to 3 - ' +
'Minor\. Category Changed to PC - Software\. -- Monday 04th December 2006 01:28:12 PM by davidp--//--' as TicketText
) t CROSS JOIN (
SELECT '-- Monday ' as dayname
UNION ALL SELECT '-- Tuesday ' as dayname
UNION ALL SELECT '-- Wednesday ' as dayname
UNION ALL SELECT '-- Thursday ' as dayname
UNION ALL SELECT '-- Friday ' as dayname
UNION ALL SELECT '-- Saturday ' as dayname
UNION ALL SELECT '-- Sunday ' as dayname
) x1
WHERE CHARINDEX(dayname, TicketText) > 0
GROUP BY TicketText
) x2
) x3
) x4
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply