-1
date ,                         rate
1/2/2014 12:15:00.0000 PM,     abc
8/2/2014 12:15:00.0000 PM,     efg
etc...

let say i have data that are updated once a week,for example:1/2/2014 is the update date, and the second week will be 8/2/2014 etc... condition: i want to write a code in sql,if the client choose the day in 4/2/2014 and it will choose to show the data in 1/2/2014.

i have tried the query more or less like this:

SELECT rate,date FROM mytable WHERE year(date)=2014 AND month(date)=2 AND day(date)=4

but it give me empty data since there is no data at 4/2/2014 and i want the query can show the data from the previous date which is 1/2/2014

user3413288
  • 1
  • 1

2 Answers2

0
select t.* from table t where
t.date between dateadd(dd,-7,t.date) and dateadd(dd,7,t.date)

would translate to

select t.* from table where 
4/2/2014 between 3/26/2014 and 4/9/2014

does that work?

macm
  • 544
  • 7
  • 21
0

You don't specify the database you are using or the data types. Let me assume that the data types are, indeed, dates. The following query gives the idea:

SELECT rate, date
FROM mytable
WHERE date <= '2014-02-04'
ORDER BY date desc
LIMIT 1;

The above gives guidance. The syntax for the date constant might vary among databases. And, the limit 1 is not supported by all databases, so this might be top 1 or where rownum = 1 or fetch first 1 rows or perhaps even something else.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thank you for reply, i want to declare only date, but the data includes time portion, what is the solution? – user3413288 Mar 13 '14 at 06:34
  • @user3413288 . . . First, I'm not sure if this makes a difference for your application. second, date operations are quite database specific, so there is no generic method. – Gordon Linoff Mar 13 '14 at 11:03