extract field

[master_student]

Hello nice people,

I have a date field in my database that contains date and time

I would like to extract only time to display it in QlikView.

Any idea, how to do that?

Thanks

[arulsettu]

try this

Timestamp(Timestamp#('08/01/2013 08:21:00 AM','DD/MM/YYYY hh:mm:ss tt'),'hh:mm:ss')

[ToniKautto · Accepted solution]

The format expression in QlikView should be either of the below options. The shorter one will work if your format variables in QlikView matches the incoming format from data base.

Notice, the data is loaded as a timestamp, the date portion is removed by using Frac(), and finally the time value is formatted.

Time(Frac(MIN_DATE_DEB))

Time(Frac(Timestamp#(MIN_DATE_DEB, 'DD/MM/YYYY hh:mm:ss')))

[ToniKautto]

This function will create something that looks like a time, but it will logically be a timestamp. The underlying number in the timestamp must be reformatted to a decimal value with 0 integer. See my example below, where the numerical format is done with Frac().

The outer function should be Time(). It should not require format pattern, as it will then revert to the applcation default pattern.

[saimahasan]

Please check whether your time field is proper date and time field...

Then you can extract time by using:

=Timestamp([Field Name],'hh:mm:ss')

As i did for now(), its working fine,

=Timestamp(Now(),'hh:mm:ss')

[Anil_Babu_Samineni]

Let's assume your date field like

MIN_DATE_DEB

08/01/2013 08:21:00

Perhaps This

Timestamp(Timestamp#(MIN_DATE_DEB, 'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

[ToniKautto]

Please see my previous comment(s) above. This is not a accurate way to reformat a timestamp into a time.

[Anil_Babu_Samineni]

Yes, I've seen that - This scenario how do we know is that is running in the morning or After noon

Because, You used simple TIME - So, It could be the time in the evening as well. This format is 12 not 24 Hours then how do we identify?

Really help needed TONI.

[sunny_talwar]

hh:mm:ss will be 24 hour format and hh:mm:ss TT is 12 hour format with PM/AM at the end. So Time alone will tell you if its morning or evening

[miguelbraga]

You can do simply this:

time(MIN_DATE_DEB) as TIME_MIN_DATE_DEB,

It will make exactly what you want without need much

Regards,

MB