Load data from certain date from database

[#1]

I want to load tables from SQL database from certain date. Since I will run the report once a month, I would like to set a 'StartDate' and a 'EndDate' variable.

For example, for one table, what I do is as follow:

Let StartDate = Date#(5/1/2013,'M/D/YYYY');
Let EndDate = Date#(5/1/2015,'M/D/YYYY');

TableTemp:
Load
[Transaction ID],
[Billed Date],
Payment,
[Paid Date];

SQL select * from Finance.Trasaction
where [Billed Date] >= $(StartDate);

TableTranctions:

Load
[Transaction ID] as ID,
[Billed Date] as BillDate,
Payment,
[Paid Date] as PaidDate
Resident TableTemp
Where [Billed Date] <= $(EndDate);

However, the Qlikview does not compare the date variable, 0 lines is loaded.

Can anyone help me with compare date field with date variable in where clause?

[Clever_Anjos]

Why don´t you do this?

TableTemp:
Load
[Transaction ID],
[Billed Date],
Payment,
[Paid Date];
SQL select * from Finance.Trasaction
where [Billed Date] between $(StartDate) and $(EndDate)?

[sunny_talwar · Accepted solution]

Try this:

LET StartDate = Chr(39) & Date(MakeDate(2013, 5, 1), 'DD-MMM-YYYY') & Chr(39);
LET EndDate = Chr(39) & Date(MakeDate(2015, 5, 1), 'DD-MMM-YYYY') & Chr(39);

[Clever_Anjos]

Let StartDate = Date#('5/1/2013','M/D/YYYY'); // you should use quotes too, to avoid the double division 5 / 1 and result / 2013
Let EndDate = Date#('5/1/2015','M/D/YYYY');

[santiago_respane]

Have you tried enclosing your date in quotations?

SQL select * from Finance.Trasaction
where [Billed Date] >= '$(StartDate)';

Kind regards,

[#6]

This works! Thank you!

So what does Char(39)& , &Char(39) represents here?

[ziadm]

SQL use the ISO 8601 Date format by default  YYYY-MM-DD

let vStartDate = '2015-06-01';

let vEndDate = '2016-06-17';

SQL SELECT 

   [Transaction ID],
[Billed Date],
Payment,
[Paid Date];

FROM dbo."DBA"

WHERE  Bill_Date >= '$(vStartDate)'       and Bill_Date <='$(vEndDate)';

[sunny_talwar]

Chr(39) = Single quote, used it to put the date withing the single quote

[sunny_talwar]

If you got what you wanted, I would suggest closing the thread by marking correct and helpful responses.

Best,

Sunny