0

I'm using logparser to retrieve data from my IIS logs.

#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2014-02-12 22:51:18 127.0.0.1 GET /auto.aspx p=es&w=tank&i=87 36910 - 127.0.0.1 Mozilla/5.0+(Windows+NT+6.3;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/32.0.1700.107+Safari/537.36 - 200 0 0 143

I have this query:

SELECT * FROM 'C:\\IISExpress\\Logs\\*.log' WHERE c-ip = '127.0.0.1' AND cs-uri-stem LIKE '%auto.aspx%' AND date = '2014-02-12'

How do I filter by time without the seconds? I want something like this:

SELECT * FROM 'C:\\IISExpress\\Logs\\*.log' WHERE c-ip = '127.0.0.1' AND cs-uri-stem LIKE '%auto.aspx%' AND date = '2014-02-12' AND IGNORE_SECONDS(time) = '22:51'
Cornwell
  • 3,304
  • 7
  • 51
  • 84
  • You could truncate your `datetime` like this maybe? `CONVERT(char(5), GETDATE(), 108)` would give `15:29` as a result. – seph Apr 23 '14 at 13:29

1 Answers1

0

Use the QUANTIZE function to truncate seconds to zero:

WHERE QUANTIZE(TO_TIMESTAMP(date, time), 60) = TIMESTAMP('2014-02-12 22:51:00')
Gabriele Giuseppini
  • 1,541
  • 11
  • 19