Cognos format date

Cognos format date DEFAULT

Resolving problems when using data from CSV files in {{}}


csv files, fix, supported formats


{:new_window: target="_blank"} {:shortdesc: .shortdesc} {:screen: .screen} {:codeblock: .codeblock} {:pre: .pre}

{: #resolving_problems_datafromcsvfiles}

You import a data asset based on a comma-separated values (CSV) file that contains dates or times, however the visualization results in {{}} are not what you expected.

What's happening

{: #whatishappening}

When you create a visualization in a dashboard that displays dates or that is filtered by dates, the results are not what you expect. For example, you have a data asset that contains one row of data for each month. However, a visualization displays only dates from January for each year. Or, you have data from one month, but the visualization displays dates for a year. The following image explains the situation.


Why it's happening

{: #whyitishappening}

The problems that you see might be caused by the format of the dates in your data, if the source for your data was a CSV file. For example, you have the following dates:

When your data is imported, {{}} tries to determine the appropriate format to use for dates. However, you might get unexpected results. The first number in each date might be interpreted either as January or as the day, leading to different results. The date is interpreted as either January 2 or February 1.

To determine whether the format of dates is the potential cause of the problem, open your original CSV data file and review the format of the dates.

How to fix it

{: #howtofixit}

If you have dates that represent a day, month, and year, change the format to be one that is supported by {{}}. Several formats are supported, but some can be ambiguous, as shown in the example above. If you format the dates in the yyyy-mm-dd format (ISO ), the dates are always interpreted correctly.

{{}} supports the ISO standard formats for dates (yyyy-mm-dd) and times. For more information about the standard, please see

Supported date formats

{: #supporteddateformats} M/d/yy MMM d, y MMMM d, y dd-MM-yy dd-MMM-yy yyyy-MM-dd

Supported time formats

{: #supportedtimeformats} h:mm a h:mm:ss a h:mm:ss a z HH:mm HH:mm z HH:mm:ss HH:mm:ss.SS HH:mm:ss z HH:mm:ss.SS z

After you change your original data asset, open the dashboard again.


Format Dates (and Times) in a Report


There are many ways a report author can format dates and times on a Cognos Analytics report. Sometimes you need to format a date in order to save space on a report.

Here are few examples of how you can apply different formats to the same date.

  • January 1,
  • Jan 1,
  • 01/01/
  • 1/1/19

Use the Data Format* property to format a date the way you want.

*Note: This page focuses on formatting dates, but you can use the same Data Format property to also format times, currency, and other data.

Format Dates with the Data Format Property

  1. On your report page, select the date object that you want to format.
  2. In the Properties pane, under Data, click the Data format ellipses.
    The Data format dialog box appears.

  3. From the Format type drop-down list, select Date, Time, or Date/Time (depending on what you want to display).

    Format type

    Default format example

    none selected11/29/11 AM
    DateNov 29,
    Date/Time11/29/11 AM
    Time AM
  4. In many cases, the default settings will serve your needs and no further action is needed. If you need to apply additional formatting to your data, proceed to the next section below.

Use the Date Style Property

Usually, selecting an option from the Date style drop-down list will achieve the formatting you want.

Date Style


Medium (default)Nov 29,
LongNovember 29,
FullTuesday, November 29,

If the Date style property doesn't achieve the date format you want, proceed to the next section below.

Use the Pattern Property to Set Advanced Date Formatting

You can use the Pattern property to specify additional date formats.

When you define a pattern, the number of symbols you use affects how the data will appear. There are different rules for text and numbers.


In the Pattern field, enter the symbols for the date format pattern you want. Separate the symbols by spaces and punctuation as you want it to appear. Refer to the following table for the most commonly used symbols.

To displaySymbolExample

day (text) in week 


E, EE, or EEE produces Tue

EEEE produces Tuesday 

day in month

d produces 29 

day in year

D produces

day number in week (1= first day) 

e produces 2 



MM produces 1 

MM produces 01 

MMM produces Jan 

MMMM produces January 

week in year


w produces 49


yy produces 11 

yyyy produces


You can specify to show full or abbreviated text.

Number of Symbols



4 or more 

full text 

EEEE produces Monday 

less than 4 

abbreviated text 

EEE produces Mon 


The number of symbols you use in a pattern sets the minimum number of digits that will appear on a report. Numbers that have fewer digits than you specify are zero-padded.

For example, if you specify dd for day of month, then the 6th of the month would appear as 06 on your report.

Text and Numbers

For values that can display as either text or numbers, such as months, you can specify which you want to appear. If you specify text, you can also specify if words are abbreviated.

Number of symbols 



3 or more 


MMMM produces January 

MMM produces Jan 

less than 3 


MM produces 01 

M produces 1 

Pattern Examples

The following table shows some examples of symbol combinations you can enter in the Pattern field to produce certain results.

Pattern Example



Nov 29



EEEE, MMMM dd, yyyy

Tuesday, November 29,

EEE, MMM d, yyyy hh:mm a

Tue, Nov 29, AM


• Date Formats and Functions (IBM)

  1. Vintage pepsi light up sign
  2. Ncaa 18 xbox one
  3. Business systems director salary
  4. Yoga platform diy
  5. Yamaha v4 115

Web Form Date Format

Postby jim wood » Thu Jun 22, pm


I'm currently working with I've created a web sheet where users will update project stage dates. Within Excel when a date is missing it shows as blank but when published in TM1 Web it displays a date that corresponds to the date value of 0. To give you more background we store the date values as numeric values and then format the cube display. In Excel I have set the format to dd/mm/yyyy. I've done some digging and it sounds like I can't do anything about it. Could somebody confirm if this is the case?

Excel view:

Excel Dates.PNG

Web view:

TM1 Web Dates.PNG



You do not have the required permissions to view the files attached to this post.

Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 bit. TM1 Version:

COGNOS LESSON 76 - convert date from YYYYMMDD TO YYYY, MM, DD

Specifying the parameter format for date, time, and time stamp

When defining reports either using the Dynamic Workload Console, Application Lab or composer, specify parameters of type date, time, and time stamp, using a specific syntax.

The following table shows the syntax you must use when defining reports containing date, time, and time stamp formats as parameters.
Prompt typeCognos® parameter formatCognos® parameter format examples
Single valueList of valuesInterval values
DateCCYY-MM-DD2012-02-032012-02-03-Value:2012-03-14Between 2012-02-03 and 2012-04-15
Timehh:mm:ss01:00:0001:00:00-Value:01:01:01Between 01:00:00 and 23:59:30
Time Stamp2012-02-03 15:05:00-Value:2012-02-03T16:01:00-Value:2012-02-03T16:00:00

Note: You must specify the parameter format exactly as they are shown in the table respecting lower case and upper case formats.


Date cognos format

Contact US


Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Contact US

date-to-string function

date-to-string function




I am trying to combine two date columns in impromptu to give the result series 7 by the way
for example to

it is important that I see the word 'to' in the column, so have converted the date to a string using
date-to-string (Valid from) + 'to'date-to-string (Date of Last Order)
This works fine, but the resultant date is not in UK format, it is yyyy-mm-dd, I need dd-mm-yyyy

Any suggestions would be gratefully received



Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

COGNOS LESSON 80 - Date Functions

Date and time Rules functions

The following Cognos TM1 functions deal with dates and times and therefore, I believe that they are important enough to warrant a quick review:

    Please note that although all of the mentioned functions are TM1 RULES functions, each can be used within a TI process as well.

    The function returns a date string like for a given serial number. Good to know because the function will return us a serial number!

    Foo=DATE(NOW, 1); # will return a string of 'YYYY-MM-DD"

    is a little more practical (in my opinion). It takes a numeric value for a given year, month, and day and returns a string in a similar format:

    Foo=DATES(,12,2); # will

    Get IBM Cognos TM1 Developer's Certification Guide now with O’Reilly online learning.

    O’Reilly members experience live online training, plus books, videos, and digital content from + publishers.

    Start your free trial


    Now discussing:

    Cognos - Report Functions

    There are various Report functions that can be used in a Cognos report.

    Some of these different report functions include −


    This function is used to return a positive or a negative number representing the number of days between the two datetime expressions. If a timestamp_exp1 < timestamp_exp2 then the result will be a –ve number.

    How to use

    _days_between(timestamp_exp1, timestamp_exp2)


    This function is used to return a number representing the number of days remaining in the month represented by the datetime expression timestamp_exp.

    How to use



    This function is used to return a datetime that is the first day of the month represented by timestamp_exp.


    This function is used to return the datetime resulting from adding integer_exp days to timestamp_exp.

    How to use

    _add_days(timestamp_exp, integer_exp)


    This function is used to return the datetime resulting from adding integer_exp months to timestamp_exp.

    How to use

    _add_months(timestamp_exp, integer_exp)


    This function is used to return the datetime resulting from adding integer_exp years to timestamp_exp.

    How to use

    _add_years(timestamp_exp, integer_exp)


    This function is used to return a number that is obtained from subtracting timestamp_exp from today's date in YYYYMMDD format (years, months, days).

    How to use



    This function is used to return the day of week (between 1 and 7), where 1 is the first day of the week as indicated by integer_exp (between 1 and 7, 1 being Monday and 7 being Sunday). Note that in ISO standard, a week begins with Monday being day 1. In North America where Sunday is the first day of the week being day 7.

    How to use

    _day_of_week(timestamp_exp, integer_exp)


    This function is used to return the ordinal for the day of the year in date_ exp (1 to ). Also known as the Julian day.

    How to use


    Like these, there are various other Report functions as well that can be used.


    5083 5084 5085 5086 5087