I am trying to query the minimum datetime from a column that is stored as
nvarchar(max). There a a few tricky things with this query (at least for me)
There is more than just the date being stored within each record.
The position of the datetime is relative - although it does always appear in the format
**(DD-MM-YY at HH:MM PM
There are multiple datetimes stored in each record - so not only do I need to locate and capture where there is a datetime, I need to find the minimum datetime within the record
I can't just change the format that the data is stored in - there is over a decade of information that is stored this way.
The column is called 'hdresp' - here is sample data:
**(03-Apr-14 at 09:44 AM email sent) -- Billy Bob: Upgrade ordered. **(02-Apr-14 at 04:16 PM email sent) -- Sammy Richards: I can give you another cable to if you think that will help but it just might be time for an upgrade. If you want to go that route I have to ask that you submit another request for New Hardware. **(02-Apr-14 at 03:17 PM email sent) -- Paul Smith: Michael Stop by my desk when you have a second.
02-Apr-14 at 3:17 PMas the minimum time and converts it to
YYYY-MM-DD HH:MM:SS- for example
Perhaps you could try this approach:
select hdresp, min(ts) as timestamp from ( select hdresp, cast(substring(hdresp,delta+3,10)+substring(hdresp,delta+15,9) as datetime2) as ts from ( select hdresp, charindex('**(',hdresp,1) as delta from problem union select hdresp, charindex('**(',hdresp,1+charindex('**(',hdresp,1)) as delta from problem union select hdresp, charindex('**(',hdresp,1+charindex('**(',hdresp,1+charindex('**(',hdresp,1))) as delta from problem union select hdresp, charindex('**(',hdresp,1+charindex('**(',hdresp,1+charindex('**(',hdresp,1+charindex('**(',hdresp,1)))) as delta from problem ) as temp1 where delta > 0 ) as temp2 group by hdresp ;
See example here: http://sqlfiddle.com/#!3/a1a99/1
If there are more than 4 possible timestamps in a hdresp, just add more
UNION SELECT... sections.
Thank you everyone for your help!
I ended up using this to extract and convert the minimum time from a string:
SELECT CONVERT(datetime, REPLACE(LEFT(RIGHT(hdresp, PATINDEX('%(**%', REVERSE(hdresp)) - 1), 21), 'at ', '')) from tblhdmain where hdindex = 211458
Which gave me the result: