InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 21:20:27 GMT20http://www.teradata.com/teradataforum/Topic9885-1-1.aspxI 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)).Fri, 16 May 2008 08:44:31 GMTSpeedsterZ28http://www.teradata.com/teradataforum/Topic9885-1-1.aspxworks for me. Are you sure your table has data for the range given? Tue, 04 Mar 2008 09:28:04 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic9885-1-1.aspxI 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...Mon, 03 Mar 2008 17:20:43 GMTjclasenhttp://www.teradata.com/teradataforum/Topic9885-1-1.aspxHi Nitin,"it's not working for me" is not helpfull at all, at least provide the error code/message.It's working for me :-)DieterThu, 29 Nov 2007 01:52:30 GMTdnoethhttp://www.teradata.com/teradataforum/Topic9885-1-1.aspxselect * 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.Wed, 28 Nov 2007 15:15:35 GMTNithyanand R Ghttp://www.teradata.com/teradataforum/Topic9885-1-1.aspxMay 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'.Wed, 28 Nov 2007 11:11:02 GMTSomeshhttp://www.teradata.com/teradataforum/Topic9885-1-1.aspxHi 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.-NitinWed, 28 Nov 2007 10:24:08 GMTnitinajmeri