Finding difference between two dates

[#1]

Dear Guru's

In the script Select Statement how we will find diffence with currdate() -datefield as DiffDate.

example: CurrDate()-DueDate as DiffDate.

Please help me...

Regards

Joe

[SunilChauhan]

use below code

today()-datefield as Diffdate

[vijay_iitkgp]

Hi Joe,

You can create a variable vcCurrentDate=Date(Today());

and use this variable in script

$(vCurrentDate)-DueDate as DiffDate.

Regards

Vijay

[#4]

Dear Sunil,

Thanks for your reply

when I try the expression, I am getting the following error.

SQL Error:[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

SQL Scriptline:

SQL State:37000

Regards

Joe

[SunilChauhan]

you can use if you using sql as backend

GetDate()- datefield  as diffdate

[Miguel_Angel_Baeyens]

Joe,

Use a LOAD statement. It has been discussed before how useful is this to control which data you put into QlikView instead of controlling with SQL statements which data you pull from your data source:

Table:
LOAD Today() - Date(DueDate) AS DateDiff;
SQL SELECT DueDate 
FROM Data Source;

Hope that helps.

Miguel

[#7]

Dear Sunil,

the same error coming again, any workaround

I tried DateDiff(Currdate(),Duedate) as DiffDate no result

Regards

Joe

[#8]

Dear Miguel,

I am trying in the following select statement, the same error appears.

Please suggest solution for the below.

ODBC CONNECT TO AS400 (XUserId is NMTLEB, XPassword is NMTTPB);

Trend:

Load

CORP,

SUB,

LOC,

PRON,

RUPEE,

Today()-Date(Duedate) as DateDiff;

SQL SELECT

CORP as Client,

SUB as  Subsidary,

LOC as  Location,

Count(PRON) as Invcount,

Sum(RUPEE) as Amount,

DateDiff

FROM S104F31D.Data

Where CORP =180

Group BY

CORP,SUB,LOC

;

Store Trend into Test.qvd;

Regards

Joe

[vijay_iitkgp]

Hi,

You need to  extract DueDate instead of DateDiff in Sql Select Statement:

See Below:

Trend:

Load

CORP,

SUB,

LOC,

PRON,

RUPEE,

Today()-Date(Duedate) as DateDiff;

SQL SELECT

CORP as Client,

SUB as  Subsidary,

LOC as  Location,

Count(PRON) as Invcount,

Sum(RUPEE) as Amount,

DueDate

FROM S104F31D.Data

Where CORP =180

Group BY

CORP,SUB,LOC,DueDate

;

Store Trend into Test.qvd;

[SunilChauhan]

use below

DATEDIFF(day,getdate(),Datefield) AS DiffDate

ex:

DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

hope this help