Extract min timestamp from string

[tubeysocks]

Hello all,

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.

Please Help

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.

-What i would like to end up with is a query that identifies '02-Apr-14 at 3:17 PM' as the minimum time and converts it to 'YYYY-MM-DD HH:MM:SS' -for example '2014-04-02 15:17:00'

Thanks!

[Rabbit]

You will first need to normalize the data by parsing each record to extract each date into a new table. Then the rest is easy once the data is normalized, it's just a matter of a MIN aggregate query.

To extract the timestamp, you will need to loop through each record and loop through each string using INSTR() to find the timestamp and SUBSTR() to extract it.

[ck9663]

Why not post some sample data with columns you have and the output you need and let's see what we can do.

Rabbit is correct, though, that you might need to normalize your data.


~~ CK

[tubeysocks]

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:

2014-04-02 15:17:00.000