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!
  • Students Click Here

*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


date-to-string function

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 01-01-2000 to 02-02-2002

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



RE: date-to-string function

I'm sure someone else will have a more elegant way of doing what you wish, but a long way round is to use Day(), Month() and Year() functions to give numeric values and then convert back using Number to String and Number to String Padded functions.

RE: date-to-string function


Sort of hoped for something a bit more glamarous but there you go!!

RE: date-to-string function

I know, it's pretty lame, and I'd love to offer a more elegant solution; perhaps someone else will chip in?

RE: date-to-string function

I think, you can change that. The format it takes it from system settings in control panel. Right now, I am not in front of the computer.
wait, I am. I forgot
So go to control panel--regioanl settings--date and change the settings there.

That should work.. But you need to apply it on date before you apply date to string..

I dont know how after your date is in dd-mm-yyyy and after it is converted to string and further manipulated as a string, it changes its format?

RE: date-to-string function

No that's not it I'm afraid, I have UK date settings already.

Thanks anyway

RE: date-to-string function

Supposing your Cognos installation directory is "Cognos"
and you want to connect to Oracle database.
Open the "orfunct.ini" in a text editor. you will find it in the folder
Open it and search for the function "date-to-string". You fill find the entry like:
exp=to_char ( ^1 , 'YYYY-MM-DD' )

Modify the entry to look like:
exp=to_char ( ^1 , 'DD-MM-YYYY' )

Now close your Impromptu session and open again. You should now get the results of "date-to-string" function in the desired format.

PS: this need to done for every installation of Cognos. OR you can copy this modified "orfunct.ini" file everywhere.
The file to eb modified depends on the database. For Oracle it is "orfunct.ini"

RE: date-to-string function


Sounded like a plan, no joy I'm afraid.  I am running a local version, so have changed just one version of orfunct.ini.  Have I forgotten anything else?



RE: date-to-string function

have you tried changing "impfunct.ini"? (same location as orfunct.ini)

RE: date-to-string function

I have now! but no luck, I have changed all the ini files that contain date formats.  There must be some default setting that is giving me yyy-mm-dd, even if I just enter a straight calculated date field I get that format.


RE: date-to-string function

I'm somewhat confused as changing impfunct.ini works for me. Our DB (Progress 9.1c) has dates as dd/mm/yy but the date-to-string function returns as yyyy-mm-dd. After amending impfunct.ini as Sharad suggests, I get dd-mm-yyyy (Impromptu 7).

RE: date-to-string function


The expression in the impfunct.ini should be changed as follows. Note the order of EXTRACT function.

Change the order from YEAR-MONTH-DAY to DAY-MONTH-YEAR. Don't forget to change the no. of characters from 4-2-2 to 2-2-4.

Amend the tip= to read dd-mm-yyyy instead of yyyy-mm-dd. This shows up in the tip box of the data definition box.

Here's how your final version should look. It's advisable to backup the ini file before amending.

exp=asciiz ( EXTRACT ( 'DAY' , ^1 ) , 2 ) + '-' + asciiz ( EXTRACT ( 'MONTH' , ^1 ) , 2 ) + '-' + asciiz ( EXTRACT ( 'YEAR' , ^1 ) , 4 )
tip=Syntax: date-to-string (date_exp) \nReturns the date_exp converted to a string of the form dd-mm-yyyy.
tip1=Date expression

RE: date-to-string function

Simple, don't change any settings.
1.get the date1 in one field and apply format as dd-mm-yyyy(right click on the field, go to format and type in as dd-mm-yyyy)
2.get the date2 in one field and apply format as dd-mm-yyyy(right click on the field, go to format and type in as dd-mm-yyyy)
3. make a calculated field as date1+'to'+date2.
4. then remove date1, date2 from the report(these will remian in your query)
does that help?

RE: date-to-string function

cognossolutions et all

I have in essence tried this already, but the only way I can see to get the 'to' in is to convert the date to a string,  if I do this I get the date format the wrong way around.  I think this is the nub of the problem, and the reason why the ini file edits don't work.

Any more suggestios anyone,  by the way thanks to you all for the suggestions so far.

RE: date-to-string function


The solution given by cognossolutions will work, but not quite in the way that poster indicated.

Using the sample "Great Outdoors" catalogs, I've successfully created your field with the 'to' label without changing the dates to strings as follows:

Put each of the two dates into the page header. Select each of the dates and apply the formatting suggested (dd-mm-yyyy) by selecting the column and right-clicking and selecting 'Format' from the dialog. Then select the 'From' date in the header and press the keyboard 'End' key to go to the end of the column. Now add a space and the word 'to' and a second space to that first date as displayed. Now drag the second date until it merges with the end of the first date. This will give you a concatenated display field in the correct format without converting the dates to strings. It is not a report query column, so you cannot do additional calculations with it, but you can now drag it back down into a list frame if you want to have it display on each output row.

Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

RE: date-to-string function

There is no escaping the fact that the date-to-string function will return a value in the form YYYY-MM-DD. No amount of manipulation, other than amending the expression itself, will help you get your date in DD-MM-YYYY format. Look closely at the formula within the date-to-string function in impfunct.ini.

exp=asciiz ( EXTRACT ( 'YEAR' , ^1 ) , 4 ) + '-' + asciiz ( EXTRACT ( 'MONTH' , ^1 ) , 2 ) + '-' + asciiz ( EXTRACT ( 'DAY' , ^1 ) , 2 )

The expression concatenates YEAR-MONTH-DAY in that order. The only way out is to change this order to DAY-MONTH-YEAR. Refer to my earlier post.

I would definitely like to see how you amended the ini file and why the amendment isn't working.

I have a strange feeling that Karen might be editing the wrong file or she might have edited only the tip= section of the ini file and not the above expression in exp=.

Similarly, if you are using oracle, then you should modify orfunct.ini the way sharad has proposed;

exp=to_char ( ^1 , 'DD-MM-YYYY' )

If these steps ain't working then surely you are missing something because there is no way that these won't work. I have tried them myself and I am sure others have too.

If this doesn't work then do a "find file" and see if you have multiple copies of impfunct.ini files on you machine for each version of impromptu. Make the change to all the ini files if you are not sure which is the right one.


RE: date-to-string function

Thanks all, busy today will check all later


RE: date-to-string function

Hi all

It's sorted, not quite sure what went wrong, copied the impfunct.ini file from another PC re-edited it as nagrajm suggested and I now have the required format.

Many thanks everyone for the help and suggestions.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close