How to fetch only month end dates from SYS_CALENDAR from Current date

Database
Enthusiast

How to fetch only month end dates from SYS_CALENDAR from Current date

Hi,

I'm new to this Forum and have been looking around for logic to fetch only month-end dates from SYS_CALENDAR.CALENDAR table.

Here's the scenario:

I've a query where in I need to fetch records for a specific period from a table with huge volume of records. This table has a field called SNAP_DT which denotes the business date.

The current query that is being used is coded in such a way where SNAP_DT IN clause is used and this part of the query is updated to include the previous month-end date (every time before execution)

I would like to modify the query in such a way that this SNAP_DT field is automatically passed with (say) the last x month-end dates (calculated from current date) [For e.g. let x = 6] for fetching records. I would want to code logic wherein the month-end dates are fetched from the SYS_CALENDAR.CALENDAR table from the current date and is passed to this SNAP_DT field via a subquery.

Can someone help me coding logic for my requirement? I use Teradata 13.11

6 REPLIES
Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

Will this be helpful for you?

select add_months(current_date -extract(day from current_date) +1,1) -1,

add_months(current_date -extract(day from current_date) +1,-1) -1,

add_months(current_date -extract(day from current_date) +1,-2) -1

........

Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

I'm not sure if I am interpretting your scenario correctly...

CREATE MULTISET TABLE some_tbl
AS (SELECT calendar_date AS SNAP_DT
    FROM Sys_Calendar."CALENDAR"
    WHERE calendar_date BETWEEN DATE-400 AND DATE
) WITH DATA;

SELECT *
FROM some_tbl tbl
WHERE tbl.SNAP_DT IN (
    SELECT calendar_date-1 AS calendar_date FROM Sys_Calendar."CALENDAR"
    WHERE day_of_month = 1 AND month_of_calendar-1
    BETWEEN MONTH_OF_CALENDAR(CURRENT_DATE)-6 AND MONTH_OF_CALENDAR(CURRENT_DATE)
);

There are date functions in Teradata (I'm using 13.10) which are very useful for readability. The subquery in the "IN" clauses returns all of the month-end dates. The catch is, I'm not sure how you are planning to implement the "last x months" part. I'm assuming you're okay with changing the "6" in your code?

Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

Hi cmedved,

That's exactly the code I was looking out for.. The subquery inside the IN clause resolves my need...

Thank you very much !!

Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

Hi,

 

  How to fetch current_date + 45th day using sys_calender function or any other alternatives?

I have to repleat this process everyday. can anyone help me on this.

 

Thanks

Teradata Employee

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

The title does not seem to match the question, but ... 45 days from today would be just CURRENT_DATE+45.  To get the month-end date of the current month, you could get the first day of the month, add one month to that, then subtract one day, viz.:

select add_months(trunc(current_date,'MM'),1)-1

or add one month to the current date, then truncate to the first of next month, then subtract a day.

select trunc(add_months(current_date,1),'MM')-1

Teradata Employee

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

Hi prasad1,

 

Next time, please open a new subject because your question is not really related to OP's one.

You didn't say what for or how do you want to fetch your data, but maybe you only need the bellow query ?

  select calendar_date
    from sys_calendar.calendar
   where calendar_date between current_date and current_date + 45
order by calendar_date asc;