|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/28/2007 10:09:46 AM
Posts: 1,
Visits: 1
|
|
Hi
I want to write a query to get results for only last week, i.e Date between (current_date -7) and current_Date. when i write above syntex it is not working for me.
Can anyone help me?
Thank in advance.
-Nitin
Nitin Ajmeri
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 9/30/2008 1:40:23 PM
Posts: 50,
Visits: 224
|
|
|
May be the date format or datatype in the column you are comparing to is different. Try casting to same format and data type. You may also try using >= , <= instead of 'between and'.
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 3/5/2008 9:54:38 AM
Posts: 67,
Visits: 147
|
|
select * from sys_calendar.calendar where calendar_date between current_date-7 and current_date order by calendar_date
The above query does return rows for me. Check the column that you are using in your where condition.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/3/2008 7:39:41 AM
Posts: 483,
Visits: 213
|
|
Hi Nitin,
"it's not working for me" is not helpfull at all, at least provide the error code/message.
It's working for me :-)
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/3/2008 5:19:43 PM
Posts: 8,
Visits: 25
|
|
I got this from someone in the forum last month as I had the same question.
...
INNER JOIN (Select calendar_date-day_of_week-6 as min_date, calendar_date-day_of_week as max_date
from sys_calendar.calendar where calendar_date = current_date) date_range
ON trans_date between date_range.min_date and date_range.max_date
...
James Clasen
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 9/4/2008 4:08:05 PM
Posts: 91,
Visits: 117
|
|
works for me. Are you sure your table has data for the range given?
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/30/2008 10:31:41 AM
Posts: 4,
Visits: 6
|
|
I perform a similar query quite often when I want to get data from the first of the month until today's date--and the way I do it is by casting my date column as a date then taking day from current_date-current_date plus one as my first day of the month and then simply current_date for today.
The code example is below:
cast(a.snapshot_date as date) BETWEEN (current_date - EXTRACT(DAY FROM current_date)+1) AND current_date;
So I'm sure this could easily be set to pull the previous week's data by just inserting the code you had for a week ago--i.e. ((current_date -7)).
|
|
|
|