ERP Time Field in FWM

[gatorfe]

Hello. I have a time field in ERP that stores the time in a six digit numeric field (hhmmss) so in ERP data shows 72223.  In Cognos I want to see it as 7:22:23.  I review the time formats in fwm and none of them convert the data to the format I am looking for.  Any suggestions?

[cognostechie]

There is a 'cast' function in FM. Try that first, if not then try this:

Assuming the name of your field is 'Date' -

If
(char_length(cast( [Database Layer].[New Query Subject].[Date],varchar(6))) = (6))
then
(
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),1,2) + ':' +
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),3,2) + ':' +
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),5,2)
)
else
(
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),1,1) + ':' +
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),2,2) + ':' +
substring(cast( [Database Layer].[New Query Subject].[Date],varchar(6)),4,2)
)

[cognostechie]

If the database is SQL Server, you could also try the 'convert' fucntion.

convert(char(6),<Date>,112)

[Lynn]

Whatever database function you have available to do data type conversion would get you what you need. You may need to convert the number to a string first, and then to a time (if the function is expecting string in and time out).

If you can get it in a time data type as opposed to character format then you would be able to more than just display in the desired format. Maybe figuring out durations etc. if that is something you'll need to do.