SQL How to Retrieve Most Recent Data On or Before a Specified Date

[pbornemeier]

I have a table with 2 columns: DateTime & Value, indexed by DateTime. A new row is created whenever a sensor's current value differs from the previous value. There are date gaps in the table.

What SQL could I use to extract the most recent value from the table that that is on or before a particular DateTime? For instance the table has entries for noon on each of 12 June 16, 14 June 16, 23 June 16 and 01 Aug 16; What was the value at 10:45 15 June 2016? Even though that DateTime is not in the table I want to retrieve the value associated with 1200 14 June.

Attempting this in Access 2010, but would appreciate any SQL's answer.

 

[anakic]

Something like this ought to do it:

SELECT
top 1 value
FROM
YourTableName
where
`DateTime` < date()
order by
`DateTime`desc


The above query uses the current date. For specific date use something like this:


SELECT
top 1 value
FROM
YourTableName
where
`DateTime` < #01/01/1970#
order by
`DateTime`desc

 

[pbornemeier]

Thanks