- SQL Server
- Oracle (provider)
- SQLite (provider)
- MySQL (provider)
- Postgresql (provider)
- DB2 (provider)
Here is the code on GitHub.
Good Programmers Copy. Great Programmers Steal.
Wednesday, May 27, 2015
OneSql PowerShell Module Fits All Relational Databases
OneSql is a PowerShell module providing a universal query interface to all relational databases, as long as there is a .NET data provider or an ODBC driver for the database. Supported databases include, but not limited to,
Saturday, May 23, 2015
Date Calculation in Oracle and SQL Server
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)
|
|
Wednesday, May 13, 2015
Understand Oracle MODEL clause in one sentence
Short Version
Oracle MODEL clause allows 3-D access to a data set, compared to regular SQL functions' 1-D access.
Long Version
Oracle MODEL clause let you access any columns in the same row (1st dimension), and in any other rows (2nd dimension). In addition, you can use the calculated results immediately in the same query, enabling sequential calculation (3rd dimension).
Implication
The 3-D access extends SQL's data calculation capability in a revolutionary leap. It is similar to compare a 3-D printer to a hand-held label printer. It enables implementation of complex business logic within SQL, which previously has to be implemented in custom functions or stored procedures, or even outside of database using other programming languages.
Subscribe to:
Posts (Atom)