Strip Hour from Timestamp

building semantic layer
1

I have a timestamp field that has a data type of date and is formatted as:
mm/dd/yyyy hh:mm:ss
I have two needs associated with this.
I would like to define two additional objects at the universe level. One that has the hour portion only and one that has the day portion only, but I haven’t been able to figure out how to do so. I applied a format to the timestamp to make it look like it is the hour only, but if you try to build a variable in the report it doesn’t like it because in the background it is still a date field, not a number.

Can anyone help please? Thank you.

Gizmo (BOB member since 2004-11-09)

2

Hi,

What database do you use?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

DB2 version 9

Gizmo (BOB member since 2004-11-09)

4

Then try using the DAY() and HOUR() funtions:

DAY(table.date_time_field)

and

HOUR(table.date_time_field)

You can also try using the EXTRACT() function:

EXTRACT(DAY FROM table.date_time_field)

and

EXTRACT(HOUR FROM table.date_time_field)

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

When I try the Day function I get the following error: The expression type is not compatible with the object type

When I try EXTRACT(DAY FROM KC.EQP_ONLN_TRIP_FACT.DERAMP_TMSTMP) I get the following error:
An unexpected token “DAY FROM KC.EQP_ONLN_TRIP_FACT.DERAMP_TMSTMP” was found following “Select Extract(”

Gizmo (BOB member since 2004-11-09)

6

The result of the DAY() function is a number (large integer to be precise) so the universe object that uses this function must be of a number type. You probably had it defined as a character type.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

7

Thank you. Your were exactly correct. I am up and running now. Thank you for your assistance.

Gizmo (BOB member since 2004-11-09)

8

Hi People,

I am sorry if my question seems to be dumb one here. But I am still new to Bo and I am to learn through other’s questions.

My question is why can’t we use substring function here to extract hour and day?

vanaml1 (BOB member since 2012-07-30)

9

I believe because substring is a character function and we are dealing with a date/time.

Gizmo (BOB member since 2004-11-09)