to_date

[Robinh]

to_date(to_char([DATABASE].[nb_queue].[approved_date], 'MM/dd/yyyy hh:mm'), 'MM/dd/yyyy hh:mm') turns the execute arrow on in a new calculation window. When I run it, I get : XQE-V5-0016 V5 syntax error found, unexpected end of expression "to_date(to_char([DATABASE].[nb_queue].[approved_date], 'MM/dd/yyyy hh:mm')" found.  Which resolves to the 10.2 fix list.

I am running 10.2 Framework Manager using a jdbc Informix datasource.

I have tried to_date(to_char([Database].[nb_queue].[approved_date],'MM/dd/yyyy') ||' '|| substr(lpad([Database].[nb_queue].[approved_time],4,0),1,2)  ||':'||  substr(lpad([Database].[nb_queue].[approved_time],4,0),-2,2)  ,'MM/dd/yyyy HH:mm') and every combinnation of () and date formats I know.

My goal is to append a hr and minute to the date to be able to compare it to another similarily formated date to get the elapsed time. The hr and minute are substringed from a int16 column representing actual time on a 24 hr clock, but not really a time field.

Is it possible that I am missing a java formatting for the date?

Any help appreciated.

Thanks

[bus_pass_man]

So, you've got a column which is a datetime data type ([nb_queue].[approved_time]?) and another column which is just a date data type (nb_queue].[approved_date]?). Is that a correct understanding?

You want to add hour and minute data to the date data type column.  Is that a correct understanding? 

That data exists in a third column whose data type is int16.  Is that a correct understanding?  If so, where are you getting that data from?

So, once everything is in datetime format, you then want to determine the interval between the two columns.   

I'm not sure what you mean by 'Which resolves to the 10.2 fix list.' Did you get the expression to work (i.e. did it not return an error)?

Apart from the syntax error, what other results are you getting?

One technique which might be useful would be to divide the expression into its components to see if you can see if they are all doing what you think they ought to do.

Have you tried extract and _make_timestamp?

[Robinh]

Thank you for responding.

The approved_time column is not a date or datetime type, it is a int16. The vendor of the software built this part of their application years ago and they record the time in this field for reference. They just strip the time off of the system time when someone approves a report. It is worthless as a comparison to any other time column. All time columns are of this type.

When I search on that error (google), all I  get is a reference to the Cognos 10.2 fix list.

I looked at _make_timestamp, but the inputs seem to be integers for year, month and date.  Yes, I did divide and check the substr, lpad,to_char to make sure they do what I want.

Thanks
Robin

[Lynn]

I am surprised you can do lpad on an integer field since lpad is a string function. There must be some implicit conversion going on behind the scenes which may or may not be helping you. It also looks like you are trying to concatenate elements with || which is also a string operation that I wouldn't think makes sense when some of the components, like the result of your to_date, are not strings.

You might try using Cognos functions instead and stick with numbers and dates where possible.

Let's pretend your approved time integer field contains 2015.

To get the hours from your integer time field, this function should give you 20:

Code Select Expand


floor ( [approved_time] / 100 )


To get the minutes from your integer time field, this function should give you 15:

Code Select Expand


[approved_time] - ( floor ( [approved_time] / 100 ) * 100 )


An alternative simplified expression for minutes is:

Code Select Expand


[approved_time] - ( [Hour] * 100 )


Finally, cast your date to a date/time datatype and use the _add functions to add the hours and minutes. Note that when you cast your date to a date/time the time portion will default to 00:00, just as the _make_timestamp function would, which is where I think buss_pass_man was going....extract out the components of your date and put them in _make_timestamp upon which you can then add the necessary hours and minutes.

Code Select Expand


_add_minutes ( _add_hours ( cast ( [approved_date], timestamp ), [Hours] ) , [Minutes] )


I don't have the same database as you, but I think these should all resolve to the equivalent functions for Informix. Or else give you some alternative ideas to pursue.