Hi
Ignacio,
The only way to do this is with a bunch of string functions. There is no time format as far as I am aware, so you need to create a date/time format. Here are the steps:
- Create a timestamp. Since you didn't specify the need for a date, we can grab the sysdate
- Extract the hour, minute, and seconds from the text string
- Add the hour. minute, and seconds to the timestamp created in Step 1
- Format the display to show as time
Step 1:
Create a calc called TimeStamp
_make_timestamp (
extract(year,current_date)
,extract(month,current_date)
,extract(day,current_date)
)
Step 2:
This is the most complex part, as you might have a single digit for an hour or minute so we need to check for that. If you know for a fact you have leading zeros, then you can just grab positions of the string to easily pick up the H/M/S.
"10:47:00 AM"
Create a calc called Hour and check where the first colon shows up. If it equals 2, you only have a single digit for the hour and should only pick up 1 char. Otherwise, expect 2 digits. We also want to be able to add these, so let's cast them into integers.
cast(
if(position (':',[TimeString]) = 2)
then(substring ([TimeString],1,1))
else(substring ([TimeString],1,2))
,integer)
Repeat for Min and Seconds
Minutes
cast(substring ([TimeString],4,2)),integer)
Sec
cast(substring ([TimeString],7,2),interger)
Step 3:
Put it all together
Create a calc called Time to add the hours, minutes, and seconds to our timestamp which defaults to a time of 00:00:00
_add_seconds (_add_minutes (_add_hours ([TimeStamp],[Hour]),[Minutes]),[Sec])
Step 4
We are done, we just want to see it as a time so adjust the properties and format the Time calc to time
Hope this helps!
------------------------------
Todd Schuman
------------------------------
Original Message:
Sent: Sat December 12, 2020 04:43 PM
From: Ignacio fortuno
Subject: Convert string to date or numeric value
Hi all,
I am trying to convert text to a date or numeric format. The data looks like this: "10:47:00 AM" I would like to create a data item that converts the text to either a true date or a number.
Thank you in advance, FYI, this is in Cognos Analytics through Ultipro
------------------------------
IgnacioF
------------------------------
#CognosAnalyticswithWatson