Post reply

Need T-SQL query help

  • September 21, 2007 at 1:10 pm

    #181005

    I need to search for a record in a table that is the "nearest" to a given date. The table to be queried contains entries of logged events which have a recorded time stamp. I need to find the closest record either before or after a given date (passed in).

    Can someone help me out?

    Thanks in advance

    Regards,
    Bob Szymanski
    Blue Mule Software

  • September 21, 2007 at 1:35 pm

    #735356

    There is probably an easier way to do this but just off the top of my head this would work:

    select

    case when abs(datediff(second, (select max(somedate) from dbo.jbmtest where somedate <= '2001-07-10 20:00:00'),'2001-07-10 20:00:00')) <

              abs(datediff(second, (select min(somedate) from dbo.jbmtest where somedate >= '2001-07-10 20:00:00'),'2001-07-10 20:00:00'))

         then (select max(somedate) from dbo.jbmtest where somedate <= '2001-07-10 20:00:00')

         else (select min(somedate) from dbo.jbmtest where somedate >= '2001-07-10 20:00:00') end

    I'm assuming "seconds" is a close enough measure or you could do milliseconds.  If you are doing this in a script then a variable for the date being search for would be easier than typing the date each time as I have.

    NOTE: The ABS() call isn't necessary if you flip the dates around in the datediff() call for the one testing >=.  You just have to insure you'll never get a negative value.  The ABS() is the lazy way of doing that.

    HTH,

    James.

  • September 21, 2007 at 1:47 pm

    #735363

    declare @min-2 float, @date datetime

    set @date = getdate()

    select @min-2 = min(abs(datediff(mi, [datetime], @date))) from

    select [datetime]

      from

    group by [datetime]

    having min(abs(datediff(mi, [datetime], @date))) = @min-2

  • September 21, 2007 at 2:25 pm

    #735367

    Thanks Alex,

    Well done. I appreciate it.

    Regards,
    Bob Szymanski
    Blue Mule Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply