First Day of the Previous
Year
TIMESTAMPADD( SQL_TSI_YEAR , -1,
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))
From right to left the
first TIMESTAMPADD returns the first day of the current year. The second
TIMESTAMPADD removes a year from the returned date for the First Day of the
Previous Year.
First Day of the Current
Year
TIMESTAMPADD( SQL_TSI_DAY ,
EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This calculation returns
the first day of the year by deducting one less than the total number of days
in the year.
First Day of the Next
Year
TIMESTAMPADD( SQL_TSI_YEAR , 1,
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))
From right to left the
first TIMESTAMPADD returns the first day of the current year. The second
TIMESTAMPADD adds a year to the date returned which will give the first day of
the next year.
First Day of the Previous
Month
TIMESTAMPADD(SQL_TSI_MONTH, -1,
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))
From right to left the
first TIMESTAMPADD returns the first day of the Current Month. The second
TIMESTAMPADD then subtracts one month from the first day of the Current Month
arriving to the First Day of the previous month.
First Day of the Current
Month
TIMESTAMPADD( SQL_TSI_DAY ,
DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This expression gets the
current day of the month and subtracts one less than the current day to arrive
at the first day of the month.
First Day of the Next
Month
TIMESTAMPADD(SQL_TSI_MONTH, 1,
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))
From right to left the first
TIMESTAMPADD returns the first day of the Current Month. The second
TIMESTAMPADD then adds one month from the first day of the Current Month
arriving to the First Day of the next month.
Last Day of the Previous
Month
TIMESTAMPADD( SQL_TSI_DAY , -(1),
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))
From right to left the
first TIMESTAMPADD returns the first day of the Current Month. The second
TIMESTAMPADD subtracts a month to arrive at the first day of the previous
month.
Last Day of Current Month
TIMESTAMPADD( SQL_TSI_DAY , -(1),
TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(
CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the
first TIMESTAMPADD finds the first day of the current Month. The second
TIMESTAMPADD adds one month to the date to arrive at the first day of the next
month. The final TIMESTAMPADD subtracts one day from the returned date to
arrive at the last day of the Current Month.
Last Day of the Next
Month
TIMESTAMPADD( SQL_TSI_DAY , -(1),
TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(
CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the
first TIMESTAMPADD finds the first day of the current Month. The second
TIMESTAMPADD adds two months to the date to arrive at the first day of month
after next. The final TIMESTAMPADD subtracts one day from the returned date to
arrive at the last day of the Next Month.
Last Day of Previous Year
TIMESTAMPADD( SQL_TSI_DAY ,
-1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1)
+ 1, CURRENT_DATE))
From right to left the
first TIMESTAMPADD returns the first day of the current year. The second
TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
Last Day of Current Year
TIMESTAMPADD(SQL_TSI_YEAR, 1,
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT(
DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the
first TIMESTAMPADD returns the first day of the current year. The second
TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The
third TIMESTAMPADD adds a single year to the date to arrive at December 31 of
the Current Year.
Last Day of the Next Year
TIMESTAMPADD(SQL_TSI_YEAR, 2,
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT(
DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the
first TIMESTAMPADD returns the first day of the current year. The second
TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The
third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the
Next Year.
First Day of Current
Quarter
TIMESTAMPADD( SQL_TSI_DAY ,
DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This was included to show the calculations
discussed above can be used with other functions. This is the same expression
as the one that returns the first day of the current month except this one uses
the DAY_OF_QUARTER property to return the first day of the current quarter.
Last Day of Current
Quarter
TIMESTAMPADD( SQL_TSI_DAY ,
-(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY ,
DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
Demonstrated using
Quarters. From right to left the first TIMESTAMPADD returns the first day of
the Current Quarter. The second TIMESTAMPADD returns the first day of the next
quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive
at the last day of the Current Quarter.
Number of days between
First Day of Year and Last Day of Current Month
TIMESTAMPDIFF(SQL_TSI_DAY,
CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1),
TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(
CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))
For simplicity I hard coded the January 1,
2010 date and CAST it to a date. I could have used the First Day of the Current
Year calculation but didn't want to over clutter the example. The second part
of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force
the TIMESTAMPDIFF to calculate the number of days between the first day of the
year and the last day of the current month.
Difference of 7 hours from the Date column
TIMESTAMPADD(SQL_TSI_HOUR,-7,TimeDate.column )
Using the Time stampdiff function throws an
error. So this is an alternative and it works
Business Days in a month
select count(trunc(sysdate,'MM') +(level-1))
from dual
where to_char(trunc(sysdate,'MM') +(level-1),'D') not in (1,7)
connect by level <=LAST_DAY(sysdate)- trunc(sysdate,'MM') + 1
For simplicity I hard coded the January 1,
2010 date and CAST it to a date. I could have used the First Day of the
This can be achieved if we have a w_day_d dimension in our rpd, but in certain situations when there is no day dimension then one of the possibilities is creating a repository variable. In the initialization block use this query and call this in the answers and this will help you in calculating the business days
Business Days Passed in the Month
select count(trunc(sysdate,'MM') +(level-1))
from dual
where to_char(trunc(sysdate,'MM') +(level-1),'D') not in (1,7)
connect by level <=sysdate- trunc(sysdate,'MM') + 1
To calculate the total number of days passed in the business days then this is how you can do. Create a repository variable and in the intialization block use this query