问题描述:

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.

  • 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

网友答案:

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:

2014-04-02 15:17:00.000
相关阅读:
Top