Date Formats and Functions

You set date formats and functions on the General tab of the Preferences dialog box.

You can specify that your model use one of the supported date settings in the following table.

Date setting	Description
Predefined	As defined in one of the following supported data sources: - an Impromptu Query Definition (IQD), IBM® Cognos® package query item or report query item, where the column has a data type specified in the database - a spreadsheet with date-formatted cells - PowerHouse® portable subfiles, with a column marked as date in the subfile dictionary Where the data source does not define the date format, such as in text files, Cognos Transformer assumes the format to be YYYYMMDD .
From Windows Control Panel	As defined in Regional Settings. The value is taken from the Short Date Style box on the Date tab, or its equivalent entry on your version of the Windows operating system. You can change this setting on the General tab of the Preferences property sheet, accessible from the File menu. When new columns are created using this setting, the Date Input Format shown on the Column property sheet mirrors the Windows Control Panel setting.

Date setting

Description

Predefined

As defined in one of the following supported data sources:

- an Impromptu Query Definition (IQD), IBM® Cognos® package query item or report query item, where the column has a data type specified in the database

- a spreadsheet with date-formatted cells

- PowerHouse® portable subfiles, with a column marked as date in the subfile dictionary

Where the data source does not define the date format, such as in text files, Cognos Transformer assumes the format to be YYYYMMDD

From Windows Control Panel

As defined in Regional Settings. The value is taken from the Short Date Style box on the Date tab, or its equivalent entry on your version of the Windows operating system.

You can change this setting on the General tab of the Preferences property sheet, accessible from the File menu. When new columns are created using this setting, the Date Input Format shown on the Column property sheet mirrors the Windows Control Panel setting.

Date Codes

To specify a date format that exactly matches that used in your source data, on the Time tab of the Column property sheet, choose one of the following Date Input Format settings.

Date input format setting	Description
YMD	Year, Month, Day; for example, 070413 or 2007-Apr-13
DMY	Day, Month, Year; for example, 130407 or 13 Apr 2007
MDY	Month, Day, Year; for example, 041307 or Apr 13, 2007
YM	Year, Month: for example, 200704 or 2007-Apr. This setting is processed as if the day component were 01.
MY	Month, Year; for example, 042007. This setting is processed as if the day component were 01.
Y	Year; for example, 2007. This setting is processed as if the month and day components were 01.

To construct dates consisting of years, quarters, months, or days, use the following codes.

Date code	Description
YY	A 2-digit year; for example, 07
YYYY	A 4-digit year; for example, 2007
Q	A 1-digit quarter; for example, 1
MM	A 2-digit month; for example, 01
MMM	The abbreviated month name; for example, Jan
MMMM	The full month name; for example, January
DD	A 2-digit day; for example, 01
DDDD	A day of the week; for example, Sunday
/, -, or a space character	Alternate separator; for example, 2007/01/01 or 2007-01-01
Any quoted string	The quoted string; for example, "(" shows an open parenthesis

Tip: You can combine codes. For example, use YYYY MM DD to show dates in the format 2007 Jan 01 and use YY "Q"Q to show dates in the format 07 Q1. For lunar years, quarters are labeled Q1-4, months are labeled 1-12 or 1-13, and days are labeled 1-28.

Date Functions

The following table describes the categories created by each supported date function setting.

Date function setting	Description
None	Use the value in the Source column.
Calendar year of 365 (or 366) days	Based on the standard calendar in the format YYYY or YY.
Lunar year of 52 weeks	Based on a lunar year, which contains exactly 52 weeks.
Calendar Quarter	Based on the standard calendar in the form YYYY Q or YY Q, where Q is the Quarter number 1-4.
Lunar Quarter	Based on lunar quarters, which contain exactly 13 weeks.
Calendar month	Based on the standard calendar, in the format YYYY/MMM or YY/MMM.
4-week (lunar) month	Based on a lunar month, which contains exactly four weeks.
4-4-5 week pattern	Based on repeating 3-month patterns containing four weeks, four weeks, and five weeks.
4-5-4 week pattern	Based on repeating 3-month patterns containing four weeks, five weeks, and four weeks.
5-4-4 week pattern	Based on repeating 3-month patterns containing five weeks, four weeks, and four weeks.
Week	Based on weeks, in the format `YYYY/MMM/DD` or `YY/MMM/DD`. When using this function, you must specify how the weeks split over month boundaries, and the day that marks the start of each week, using the Time tab of the Drill Category property sheet.
Day	Use format YYYY/MMM/DD or YY/MMM/DD.

Tip: Because lunar years are one or two days shorter than calendar years, periods that use the Lunar year of 52 weeks, Lunar Quarter, Lunar Month, 4-4-5 week pattern, 4-5-4 week pattern, or 5-4-4 week pattern date functions leave unassigned days that, over several years, comprise entire weeks. You can manage these unassigned days by selecting one of the available Add an extra week settings on the Drill Category property sheet for the appropriate drill-down path.