Oracle
In Oracle, the trick of date calculation is to align a date to its appropriate baseline with TRUNC() function, and then make adjustment by days and/or months. Here are some examples.
Date
|
Oracle
|
|
Week
|
First day (Sunday)
|
trunc(sysdate, 'd')
|
First workkday (Monday)
|
trunc(sysdate, 'd') + 1
|
|
Last workday (Friday)
|
trunc(sysdate, 'd') + 5
|
|
Last day (Saturday)
|
trunc(sysdate, 'd') + 6
|
|
Previous Sunday
|
trunc(sysdate, 'd') - 7
|
|
Previous Monday
|
trunc(sysdate, 'd') - 6
|
|
Previous Friday
|
trunc(sysdate, 'd') - 2
|
|
Previous Saturday
|
trunc(sysdate, 'd') - 1
|
|
Month
|
First day
|
trunc(sysdate, 'mm')
|
Last day
|
add_months(trunc(sysdate, 'mm'), 1) - 1
|
|
Previous 1st
|
add_months(trunc(sysdate, 'mm'), -1)
|
|
Previous last
|
trunc(sysdate, 'mm') - 1
|
|
Quarter
|
First day
|
trunc(sysdate, 'q')
|
Last day
|
add_months(trunc(sysdate, 'q'), 3) - 1
|
|
Previous 1st
|
add_months(trunc(sysdate, 'q'), -3)
|
|
Previous last
|
trunc(sysdate, 'q') - 1
|
|
Year
|
First day
|
trunc(sysdate, 'y')
|
Last day
|
add_months(trunc(sysdate, 'y'), 12) - 1
|
|
Previous 1st
|
add_months(trunc(sysdate, 'y'), -12)
|
|
Previous last
|
trunc(sysdate, 'y') - 1
|
|
Fiscal Year
7/1-6/30 |
First day
|
add_months(trunc(add_months(sysdate, -6), 'y'), 6)
|
Last day
|
add_months(trunc(add_months(sysdate, 6), 'y'), 6) - 1
|
|
Previous 1st
|
add_months(trunc(add_months(sysdate, -18), 'y'), 6)
|
|
Previous last
|
add_months(trunc(add_months(sysdate, -6), 'y'), 6) - 1
|
|
SQL Server
In SQL Server, the basic strategy of date calculation is to compare a date against day 0 (01/01/1900, Monday). First, calculate the difference between day 0 and today at a particular unit (year, month, week, etc). Then add the difference back to day 0. You can adjust the difference and/or shift day 0 to reach the final result. Examples follow.
Date
|
T-SQL
|
|
Week
|
First day (Sunday)
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1)
|
First workkday (Monday)
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), 0)
|
|
Last workday (Friday)
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), 4)
|
|
Last day (Saturday)
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), 5)
|
|
Previous Sunday
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), -8)
|
|
Previous Monday
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), -7)
|
|
Previous Friday
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), -3)
|
|
Previous Saturday
|
DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2)
|
|
Month
|
First day
|
DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
|
Last day
|
DATEADD(mm, DATEDIFF(mm, 0, getdate()) + 1, -1)
|
|
Previous 1st
|
DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)
|
|
Previous last
|
DATEADD(mm, DATEDIFF(mm, 0, getdate()), -1)
|
|
Quarter
|
First day
|
DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0)
|
Last day
|
DATEADD(qq, DATEDIFF(qq, 0, getdate()) + 1, -1)
|
|
Previous 1st
|
DATEADD(qq, DATEDIFF(qq, 0, getdate()) - 1, 0)
|
|
Previous last
|
DATEADD(qq, DATEDIFF(qq, 0, getdate()), -1)
|
|
Year
|
First day
|
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
|
Last day
|
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 364)
|
|
Previous 1st
|
DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
|
|
Previous last
|
DATEADD(yy, DATEDIFF(yy, 0, getdate()), -1)
|
|
Fiscal Year
7/1-6/30 |
First day
|
cast(datename(year, dateadd(month, -6, getdate())) + '0701' as
datetime)
|
Last day
|
cast(datename(year, dateadd(month, 6, getdate())) + '0630' as
datetime)
|
|
Previous 1st
|
cast(datename(year, dateadd(month, -18, getdate())) + '0701' as
datetime)
|
|
Previous last
|
cast(datename(year, dateadd(month, -6, getdate())) + '0630' as
datetime)
|
|
OBIEE
In OBIEE, the calculation strategy is the same as SQL Server.
Date
|
OBIEE
|
|
Day
|
Today
|
CURRENT_DATE
|
Yesterday
|
TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)
|
|
Week
|
First day (Sunday)
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
|
First workkday (Monday)
|
TimestampAdd(SQL_TSI_WEEK,
TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE),
DATE'1900-01-01')
|
|
Last workday (Friday)
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-05')
|
|
Last day (Saturday)
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-06')
|
|
Previous Sunday
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-24')
|
|
Previous Monday
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-25')
|
|
Previous Friday
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-29')
|
|
Previous Saturday
|
TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-30')
|
|
Month
|
First day
|
TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
|
Last day
|
TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-31')
|
|
Previous 1st
|
TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-01')
|
|
Previous last
|
TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
|
|
Quarter
|
First day
|
TimestampAdd(SQL_TSI_QUARTER,
TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE),
DATE'1900-01-01')
|
Last day
|
TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-03-31')
|
|
Previous 1st
|
TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-10-01')
|
|
Previous last
|
TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
|
|
Year
|
First day
|
TimestampAdd(SQL_TSI_YEAR,
TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE),
DATE'1900-01-01')
|
Last day
|
TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR,
DATE'1900-01-01', CURRENT_DATE), DATE'1900-12-31')
|
|
Previous 1st
|
TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-01-01')
|
|
Previous last
|
TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR,
DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
|
|
Fiscal Year
7/1-6/30 |
First day
|
CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6,
CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
|
Last day
|
CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, 6,
CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)
|
|
Previous 1st
|
CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -18,
CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
|
|
Previous last
|
CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6,
CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)
|
|
No comments:
Post a Comment