All Forums UDA
nitinajmeri 1 post Joined 11/07
28 Nov 2007
Date range

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

Someshnr 53 posts Joined 06/07
28 Nov 2007

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'.

nithyanandam 65 posts Joined 10/04
28 Nov 2007

select * from sys_calendar.calendar where calendar_date between current_date-7 and current_date order by calendar_dateThe above query does return rows for me. Check the column that you are using in your where condition.

dnoeth 4628 posts Joined 11/04
29 Nov 2007

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

Dieter

jclasen 5 posts Joined 11/05
03 Mar 2008

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_datefrom sys_calendar.calendar where calendar_date = current_date) date_rangeON trans_date between date_range.min_date and date_range.max_date...

Jim

j355ga 100 posts Joined 12/05
04 Mar 2008

works for me. Are you sure your table has data for the range given?

Jeff

SpeedsterZ28 5 posts Joined 05/08
16 May 2008

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:[code]cast(a.snapshot_date as date) BETWEEN (current_date - EXTRACT(DAY FROM current_date)+1) AND current_date; [/code]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)).

You must sign in to leave a comment.