Oracle to SAP HANA Date Conversions

-
Are you migrating your reports and dashboards from Oracle to SAP HANA? 

Below are some ready to use date conversion SQL formulas which would save your significant amount of time.

Please note that I have considered Financial year from 1-April to 31 March for year calculations. Also, number of days Calculations are considered till previous day.

1) Current Financial year start
e.g. 1-Apr-2017 
ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3)

2) Previous Financial Year start
 ADD_YEARS(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), -1) 

3) First date of current Month
  ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1)), 1)  

4)First date of current Month Previous year
  Add_months(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1)), 1), -12)

5)Number of days till date This Financial year
 DAYS_BETWEEN(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), current_date)

6)Number of days till date Previous Financial Year

  DAYS_BETWEEN(ADD_YEARS(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), -1), ADD_YEARS(CURRENT_DATE,-1))

7)Number of days in Month till previous day  

DAYS_BETWEEN (  ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1)), 1), CURRENT_DATE)

8)Total Days in current fiscal year
  DAYS_BETWEEN(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), ADD_years(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), 1))  

9)Total Days in previous financial year
DAYS_BETWEEN(ADD_YEARS(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), -1), ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3))  

10)Total number of days in Month this year
  to_number(TO_VARCHAR(LAST_DAY(Add_days(CURRENT_DATE, -1)), 'DD'))  

 11)Total number of days in Month Previous year
to_number(TO_VARCHAR( Last_day( ADD_MONTHS(Add_days(CURRENT_DATE, -1),-12)),'DD'))

 12)Number of days remaining in month 
  to_number(TO_VARCHAR(LAST_DAY(Add_days(CURRENT_DATE, -1)), 'DD')) - to_number(TO_VARCHAR(Add_days(CURRENT_DATE, -1), 'DD')) 
Thanks for reading. Do let me know if you have any concern or suggestions on above conversions.



Comments

Post a Comment

Popular posts from this blog

SAP BO Universe Performance tunning guidelines

-
Below are Universe performance tuning guidelines : These guidelines need to followed with Webi performance tuning guidelines given separately on this blog to get good performance reports. • Always use aggregate functions i.e.  Sum, max, min etc with measure objects. • Set the proper query limits at the universe, especially “Limit size of result set to” & “Limit execution time to”. Setting the right parameters will result in a good universe by performance wise • Create aggregated tables for reports taking too much time to execute due to huge data set in underline tables. • List of values: Create LOV’s on small lookup/master tables instead of actual fact table. Avoid LOV on measure and date objects. Remove LOV from non required objects i.e. keep LOV on objects which are going to be used in filters. • Use conditional objects/filters on Universe level to filter out non required data from universe level instead of reports • Push complex calculation of object...
Read more

SAP BO Universe Best Practice check list

-
Below are best practice guidelines for Universe development • Universe Name & Universe Description should be defined with proper Business Name and description in the Universe properties. • Class name should be started in INIT cap. • Objects name should be started in INIT cap. • Object should be placed in alphabetical order in the class. • Measure Objects in any class should be placed after the dimensions/detail objects. • Always define every object with meaningful description so that it helps user easily understand the purpose of it which avoids going through heavy documentation. • Measure objects should be created with aggregate functions like sum, max, min etc. This will ensure that only required aggregated data is returned to reports instead of whole set of raw data. • Create the context with proper business logic names. It will help you to find out what context is used in a particular report. • Always place any derived tables or isolated tables down ...
Read more