Date range

UDA

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
6 REPLIES
Enthusiast

Re: Date range

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

Re: Date range

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.
Senior Apprentice

Re: Date range

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
Enthusiast

Re: Date range

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
...
Teradata Employee

Re: Date range

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

Re: Date range

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