How to Solve this? related to time

[#1]

Hi All,

I have a issue.

=Timestamp('11:12:55','HH:MM') // it will return 11:12

But in my case, It is not give correct result.

I have date column "EndTime" and I wrote the expression like =Timestamp(EndTime,'HH:MM') in text object. but it is not give a correct result.

Wanted result is= 2:06

Pls Help in this..

Thanks In advance..

Regards,

Kabilan K.

[swuehl · Accepted solution]

Try

=Timestamp(EndTime,'hh:mm')

to avoid showing the Month instead of minutes.

[#3]

Hi swuehl,

Thank You ...

Regards,

Kabilan K.

[#4]

Hi All,

I have another one issue.

=Interval('00:45'-'00:44','mm') // It will return 01 ryt?

But for me it is not coming.

I have used like this.

But it returns , 00.

I have written the script for StartTime and EndTime like this:

,Timestamp(BaseDate,'hh:mm:ss') as EndTime

           ,If(Key_Counter=1,Timestamp(BaseDate,'hh:mm:ss'),Previous(Timestamp(BaseDate,'hh:mm:ss'))) as StartTime

Please Help me.

Thanks In advance.

Regards,

Kabilan K.

[swuehl]

Your end time is more precisely 00:45:03 and your start time 00:44:10, right?

So using a format 'mm:ss', you should get 00:53 as result. And using only 'mm' as format code, you'll get 00

In short, your numbers are not rounded.

[#6]

Hi,,

but want a minutes except seconds.

That is y , I have changed like hh:mm

Try

=Timestamp(EndTime,'hh:mm')

to avoid showing the Month instead of minutes.(your reply)

So that I wrote a exprsssion like this,

=interval(Timestamp(EndTime,'hh:mm')-Timestamp(startTime,'hh:mm'),'mm')

here Timestamp(EndTime,'hh:mm') // it will return 00:45

Timestamp(startTime,'hh:mm') // it will return 00:44

So, I want a result as 01, but it is not coming.

Please help me..

Thanks in advance

Regards,

Kabilan K

[swuehl]

Hi,,

but want a minutes except seconds.

That's what you already have. You want the numbers rounded to the next minute (currently, it's just truncated).

So maybe something like

=Interval( round('00:45:03'-'00:44:10',interval#('01','mm')) ,'mm')

or

=Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')

Please note also, that using something like

Timestamp(BaseDate,'hh:mm:ss') as EndTime

in your load script is only formatting EndTime with given format code, it does not remove the date from its internal numerical representation. So if you subtract EndTime and StartTime, then format the result using interval(), your result will show quite large numbers if your original BaseDates are differing in date also.

[#8]

Hi,

Now it is works. Thank u.

And please explain me Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')

Here, interval#('01','mm'))  what ll it do?

Thank u so much..

Regards,

kabilan k.

[swuehl]

The date and time functions ending with # are to reading in or interpreting date and time values in QV, while the ones without # are formatting values. There are always pairs of them: date() / date#(), time() / time#(), interval#() / interval()

So interval#('01','mm') is interpreting the text value 01 as minutes and stores the result as dual value, i.e. as a value that has a numeric part as well as a text part.

You can also just use something like 1/24/60 instead, which will return the fraction of 1 minute compared to a day (24 hours a 60 minutes) and should be identical to the numerical part of above interval#() expression.

[#10]

Hi Swuehl,

Here, I have problem.

I worte a script like this.

Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')   // Your Script

it is fine with this data: EndTime:23:04:59

                                 StartTime:23:03:45

about this data it returns 1

But it is not fine with this data: EndTime:23:04:59

                                             StartTime:23:03:06

about this data it returns 0

But I want this also 1.

so that I have changed the script like below.

Interval(Ceil(EndTime-StartTime,interval#('01','mm')),'mm')

but it returns also 1 for this data : EndTime:23:04:06

                                                  StartTime:23:04:08

but here I want the value as 0

Please help me.

Thanks in advance.

Regards,

Kabilan K.