date-to-string function 2

[karengarrette]

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]

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]

Tnanks

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

 

[drlex]

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

 

[Shek]

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]

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

Thanks anyway

 

[sharadkg]

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]

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]

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

 

[karengarrette]

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]

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

 

[nagrajm]

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]

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]

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]

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"

http://www.decisionsupportgroup.com

 

[nagrajm]

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]

Thanks all, busy today will check all later

Karen

 

[karengarrette]

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