Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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 Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

karengarrette (TechnicalUser) (OP)
25 Jul 02 5:39
Hi

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

Thanks

Karen
drlex (TechnicalUser)
25 Jul 02 8:03
Karen,
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.
HTH
lex
karengarrette (TechnicalUser) (OP)
25 Jul 02 9:21
Tnanks

Sort of hoped for something a bit more glamarous but there you go!!
drlex (TechnicalUser)
25 Jul 02 11:16
I know, it's pretty lame, and I'd love to offer a more elegant solution; perhaps someone else will chip in?
Shek (Programmer)
27 Jul 02 10:34
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?




karengarrette (TechnicalUser) (OP)
29 Jul 02 2:40
No that's not it I'm afraid, I have UK date settings already.

Thanks anyway
Helpful Member!  sharadkg (Programmer)
30 Jul 02 8:41
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
"Cognos"\cer1\bin
Open it and search for the function "date-to-string". You fill find the entry like:
[date-to-string]
1=DT;datetime_exp
exp=to_char ( ^1 , 'YYYY-MM-DD' )

Modify the entry to look like:
[date-to-string]
1=DT;datetime_exp
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"
Regards,
Sharad
karengarrette (TechnicalUser) (OP)
1 Aug 02 3:20
Sharad

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?

Thanks

karen
drlex (TechnicalUser)
1 Aug 02 3:53
Karen,
have you tried changing "impfunct.ini"? (same location as orfunct.ini)
karengarrette (TechnicalUser) (OP)
1 Aug 02 4:11
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.

Karen

drlex (TechnicalUser)
1 Aug 02 5:07
Karen,
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).
lex
Helpful Member!  nagrajm (MIS)
1 Aug 02 11:57
Karen,

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.

[date-to-string]
label=date-to-string
param=1
return=CH
1=DA,DT;date_exp
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
CognosSolutions (Programmer)
1 Aug 02 16:16
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?
karengarrette (TechnicalUser) (OP)
5 Aug 02 2:52
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.
griffindm (IS/IT--Management)
5 Aug 02 9:40
karengarrette,

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"
   
www.decisionsupportgroup.com

nagrajm (MIS)
5 Aug 02 10:45
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;

date-to-string]
1=DT;datetime_exp
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.

Thanks
karengarrette (TechnicalUser) (OP)
5 Aug 02 10:52
Thanks all, busy today will check all later

Karen
karengarrette (TechnicalUser) (OP)
6 Aug 02 4:35
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.

Karen

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!

Back To Forum

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