Does Your Database Know Which Week It Is?

Where can you find the SAP BusinessObjects BI platform maintenance schedule calendar?

SAP maintains a release calendar for the SAP BusinessObjects BI platform, indicating by week when various support packs and patches are scheduled to arrive. For example, SAP BusinessObjects BI 4.2 SP8 is supposed to arrive in week 7 of 2020.

SAP BusinessObjects Maintenance Schedule

It will be the first release of BI 4.2 that does not sport new features and will only deliver bug fixes and additional platform support (browsers, databases, operating systems, etc.). If you haven’t patched in a while, BI 4.2 SP8 should be a safe place to hang out while you wait for SAP BI 4.3 (currently in beta) to become generally available (GA).

But which week is the current week of the year? Your database has functions that can help.

For Microsoft SQL Server:

SELECT datepart(week, getdate())

For Oracle:

SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL (ISO week)

OR

SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL (starts on January 1)

According to Wikipedia, ISO Weeks ‘IW’ “start with Monday. Each week’s year is the Gregorian year in which the Thursday falls.” So for 2020, the ISO week started on Monday, December 30, 2019 while the standard week ‘WW’ started on Wednesday, January 1, 2020.

For SAP HANA:

SELECT WEEK(CURRENT_DATE) FROM dummy

According to my databases, we are in week 3 of the year, so four more weeks to go before we can download the next patch release of the SAP BI platform.

Microsoft SQL Server and INFORMATION_SCHEMA

Looking for Microsoft SQL Server metadata?

Most of my database experience is with Oracle; however, I’m currently helping a client that uses SQL Server 2005. I’m used to obtaining metadata by querying ALL_TABLES and ALL_TAB_COLUMNS (or USER_TABLES/USER_TAB_COLUMNS or DBA_TABLES/DBA_TAB_COLUMNS). How can similar information be obtained from SQL Server using similar techniques to ALL_TABLES/ALL_TAB_COLUMNS in Oracle?

 

Microsoft SQL Server Information SchemaMetadata can be queried in SQL Server using the INFORMATION_SCHEMA. For example, the following query identifies columns in one table that do not exist in another (actually, a view based on the table).

— INFORMATION_SCHEMA query to identify columns missing in database view but present in source table.SELECT source.TABLE_CATALOG, source.TABLE_SCHEMA, source.TABLE_NAME, source.COLUMN_NAME, source.ORDINAL_POSITION, SOURCE.data_type
FROM
INFORMATION_SCHEMA.COLUMNS source
WHERE
source.TABLE_CATALOG =‘FACT’
AND
source.TABLE_SCHEMA =‘dbo’
AND
source.TABLE_NAME =‘FCT_FREIGHT’
AND
NOTEXISTS
(
SELECT
‘X’
FROM
INFORMATION_SCHEMA.COLUMNS missing
WHERE
missing.TABLE_CATALOG =‘FACT’
AND
missing.TABLE_SCHEMA =‘dbo’
AND
missing.TABLE_NAME =‘FCT_FREGHT_EV’
AND
missing.COLUMN_NAME = source.COLUMN_NAME
)
ORDERBY source.ORDINAL_POSITION

This is just one application of using database metadata. Visit MSDN, the Microsoft Developer’s Network, for more information about the INFORMATION_SCHEMA.