topic Re: Date subtraction in QlikView

Date subtraction

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have date field called 'receivedate' in my table with time stamp like 10/02/2016 12:10:45 am..........

i want to subtract from present date&time to my datefiled. i tried by using now function.

now()-receivedate but it is not giving correct output.

Re: Date subtraction

sunny_talwar — Wed, 06 Apr 2016 09:10:45 GMT

What result did you get? Can you share your output and what you expected?

Re: Date subtraction

sunny_talwar — Wed, 06 Apr 2016 09:14:01 GMT

I am getting 56 05:02:10 based on EST... using this:

=Interval(Now() - TimeStamp#('10/02/2016 12:10:45 AM', 'DD/MM/YYYY hh:mm:ss TT'), 'D hh:mm:ss')

Re: Date subtraction

Wed, 06 Apr 2016 09:19:06 GMT

Hi Please find the attached excel

Here i have date field and my output is there.

Re: Date subtraction

Wed, 06 Apr 2016 09:20:30 GMT

I need to get date along time stamp

Re: Date subtraction

sunny_talwar — Wed, 06 Apr 2016 09:23:29 GMT

Is this your output (within the blue circle)

How do you interpret this? Year 1900?

Re: Date subtraction

Wed, 06 Apr 2016 09:24:28 GMT

yes that is my output

Re: Date subtraction

PradeepReddy — Wed, 06 Apr 2016 09:28:57 GMT

small modification to sunny solution..

=TimeStamp(Interval(Now()- TimeStamp#('9/28/2015 12:36:54 PM', 'MM/DD/YYYY hh:mm:ss TT'), 'D hh:mm:ss'))

Re: Date subtraction

Wed, 06 Apr 2016 09:31:47 GMT

09-07-1990 02:24:23 i am getting like this.

i think this is not correct

Re: Date subtraction

sunny_talwar — Wed, 06 Apr 2016 09:34:07 GMT

I would recommend just using TimeStamp or Date (and get rid of Interval

=Timestamp(Now() - TimeStamp#('10/02/2016 12:10:45 AM', 'DD/MM/YYYY hh:mm:ss TT'))

=Date(Now() - TimeStamp#('10/02/2016 12:10:45 AM', 'DD/MM/YYYY hh:mm:ss TT'))

Can be done in script, like this:

Timestamp(Now() - TimeStamp#(FieldName, 'DD/MM/YYYY hh:mm:ss TT')) as TimeStampDiff,

Date(Now() - TimeStamp#(FieldName, 'DD/MM/YYYY hh:mm:ss TT')) as DateDiff,

Re: Date subtraction

Anonymous — Wed, 06 Apr 2016 10:15:18 GMT

tried something like this

but not sure about the result .i got year as 1900

Timestamp(

Timestamp#( '10/02/2016 10:45:10')

Timestamp(Timestamp#(Now(),'D/M/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss ')

,'DD/MM/YYYY hh:mm:ss ')

Re: Date subtraction

settu_periasamy — Wed, 06 Apr 2016 10:32:55 GMT

When you find the difference between your date, it should give the result as number. Not in Timestamp format.

LOAD RecievedDate,

Now()-RecievedDate as Diff

From YourSource;

Re: Date subtraction

achiever_ajay — Wed, 06 Apr 2016 10:50:11 GMT

Hi RGV,

Your expression should be changed slightly as below.

=Timestamp(

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

Timestamp(Timestamp#('10/02/2016 10:45:10','DD/MM/YYYY hh:mm:ss'))

,'DD/MM/YY hh:mm:ss ')

Which Gives you result as 24/02/00 08:04:31 :::

Which to be interpreted as

24 days

2 Months

00 Years

8 Hrs

4 Min

31 sec

as the difference between DATE defined & NOW();

Hope it helpful.

Regards,

Ajay Kumar

+65-9390 2241

rgv rand wrote:

tried something like this

but not sure about the result .i got year as 1900

Timestamp(

Timestamp#( '10/02/2016 10:45:10')

-

Timestamp(Timestamp#(Now(),'D/M/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss ')

,'DD/MM/YYYY hh:mm:ss ')

Re: Date subtraction

Anonymous — Wed, 06 Apr 2016 12:20:58 GMT

Hi AjayKumar,

Thanks for the changes in the expression .