Adding or substacting hours from timestamp

[sridhar_sigired]

Hi,

I have a date with timestamp, where i need to show that -3 hours from actual date.

I have tried with different date functions but we can able to do.

Ex: 07/10/2016 01:00:00 000

Output should be 07/09/2016 22:00:00 000;

Can you please give any suggestions?

[Chanty4u]

try this

= date ( date#(' 07/10/2016 01:00:00 000' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss')

& chr(10) &

date ( date ( date#(' 07/10/2016 01:00:00 000' , 'DD/MM/YYYY hh:mm:ss') , 'DD/MM/YYYY hh:mm:ss') +(-180/24/60) , 'DD/MM/YYYY hh:mm:ss')

edited

[Chanty4u]

or else  try below

Timestamp#(YourFieldName, 'YYYY-MM-DD hh:mm:ss') AS OldTime,

Timestamp(YourFieldName+Time#(-3,'hh')) as NewTime

[fneumann]

Hi!

This seems to work:

Timestamp([Date_Time]-(3/24)) as Timestamp_substracted

[sridhar_sigired]

Hi,

I am receiving blank information when i copied this in text box.

[sridhar_sigired]

Still not getting result

[effinty2112]

Hi Sridar,

Try:

=Timestamp(Timestamp#('07/10/2016 01:00:00 000','MM/DD/YYYY hh:mm:ss fff') - 1/8,'MM/DD/YYYY hh:mm:ss fff')

Cheers

Andrew

[deepakqlikview_123]

=date ( date#('07/10/2016 01:00:00' , 'MM/DD/YYYY hh:mm:ss') , 'MM/DD/YYYY hh:mm:ss')

& chr(10) &

date ( date ( date#('07/10/2016 01:00:00' , 'MM/DD/YYYY hh:mm:ss') , 'MM/DD/YYYY hh:mm:ss') + (-180/24/60) , 'MM/DD/YYYY hh:mm:ss')

[sunny_talwar · Accepted solution]

I think first thing to check here is if your timestamps are read correctly by QlikView. If it has doing as simple as this could work:

TimeStamp(TimeStampField + MakeTime(3)) as NewField

or this in front end

=TimeStamp(TimeStampField + MakeTime(3))

But if timestamps are not interpreted correctly, try to fix that issue before you do this manipulation using TimeStamp#() or Date#() function

Get the Dates Right

Why don’t my dates work?

[sridhar_sigired]

Thanks Sunny.