I have a table
ID | DATE
1 | 16-01-2012
2 | 17-01-2012
3 | 18-01-2012
4 | 22-01-2012
5 | 28-01-2012
6 | 02-02-2012
My task is:
I have a date variable which is initialised to 16-01-2012 and I have to find all rows which are in continuum with 16-01-2012 i.e 17-01-2012 and 18-01-2012.
Please help.
One possibility would be to use a hierarchical query like the following:
with data (id, date)
as
(
select id, date from yourtable where date = '16-01-2012'
UNION ALL
select t.id, t.date
from yourtable t
inner join data d
on DATEDIFF(dd, d.date, t.date) = 1
-- 1 day difference
)
select
*
from
data
;
I don't have an SQL server here to try this out, so there might be some errors in the statement. It should give you an idea though.
Try this:
select id, date
from table
where date > date_from_variable
The only think you have jet to do, is to cast your variable to the right date format.
sample:
cast(date_from_variable, smalldate)
