How can I convert a minutes after midnight to time

[Skykit]

Hi all,
I have no idea what the term for this type of number is which is why it is making searching difficult if it has been asked/answered before.

I have a scenario where the time comes in as a character field.  The number is the minutes from midnight.
Eg: 630 would be 10:30
ie: 630/60 = 10.5
10 = hour
5 = 1/2 hour

I know how to do this in SQL but can't figure out the same in Cognos RS (relational)

Sql syntax would be : SELECT CONVERT(TIME, DATEADD(MINUTE, 630, 0))

Hope this makes sense

thanks!
AJ

[mel4363]

Hi AJ,

Try this (['DataItem or Column'],Interval Second,Interval Hour to Second)

Hope this helps

Mel

[Skykit]

Thanks for the response - no go unless I'm doing it wrong.  Syntax used is:
([Business View].[WMS_event].[history_create_time], Interval Second, Interval Hour to second) but it does not parse.

[MFGF]

Thanks for the response - no go unless I'm doing it wrong.  Syntax used is:
([Business View].[WMS_event].[history_create_time], Interval Second, Interval Hour to second) but it does not parse.

I have no idea what that expression is supposed to do - it looks a little like arguments you might pass to some function but there's no function name. I have quickly scanned through the inbuilt Cognos functions, the Business date/time functions and the vendor-specific functions and I can't see any that expect these arguments, so I'm at a bit of a loss to know what the suggestion should have been :)

So... deep breath...

cast(cast(floor([Business View].[WMS_event].[history_create_time] / 60),varchar(2)) + ':' + cast([Business View].[WMS_event].[history_create_time] - (floor([Business View].[WMS_event].[history_create_time] / 60) * 60),varchar(2)) + ':00', time)

This ought to work if you drag in a query calculation and use this as the expression :)

Basically it divides the value by 60 and ditches the decimal places to get the hours, then subtracts the hours multiplied by 60 from the original value to get the minutes, then takes these and casts them as a character field in the format h:m:s (where the seconds part is always zero), then finally casts this as a time field.

Phew. :)

MF.

[Lynn]

Here is a similar take on what the muppet provided except it is using trunc and mod instead of floor and subtraction.

Code Select Expand


cast(
  cast ( cast ( trunc ( [Business View].[WMS_event].[history_create_time] /60, 0 ), integer ), char(2) )
  || ':' ||
  cast ( mod ( [Business View].[WMS_event].[history_create_time], 60 ), char(2) )
, time )


[MFGF]

Here is a similar take on what the muppet provided except it is using trunc and mod instead of floor and subtraction.

Code Select Expand


cast(
  cast ( cast ( trunc ( [Business View].[WMS_event].[history_create_time] /60, 0 ), integer ), char(2) )
  || ':' ||
  cast ( mod ( [Business View].[WMS_event].[history_create_time], 60 ), char(2) )
, time )


Ahhh - very neat! Very neat indeed! :) I applaud you, madam! :)

I was trying to use only Cognos built-in functions and nothing specific to any database. I didn't know Trunc() was a SQL Server function - I have only seen it used against DB2. I live and learn :)

Cheers!

MF.

[Skykit]

Sorry for the AWOL!
The final statement did work as required and the end result was successful, thanks a lot for the help!