topic Re: Date variable not recognized by Oracle SQL in QlikView

Date variable not recognized by Oracle SQL

Mon, 05 Nov 2012 06:30:26 GMT

Hi,

Below said script does not load any records. I have Oracle as the sourcing database.

LET vFromDate = DayStart(Today());

BATCHLOG:
LOAD PK, Date1, Field2, Summation;
SQL
     SELECT PK, Date1, Field2, SUM(Field2) AS Summation
     FROM MY_TABLE
     WHERE Date1 >= $(vFromDate)
     GROUP BY PK, Date1, Field2;

Below is my setting:

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MMM-YYYY';

// SET TimestampFormat='DD-MMM-YYYY h:mm:ss[.fff] TT';

SET TimestampFormat='DD-MMM-YYYY h:mm:ss';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: Date variable not recognized by Oracle SQL

sushil353 — Mon, 05 Nov 2012 06:54:31 GMT

Hi Usmansheriff,

check your database date format. Is it matching with your variable format.

Variable could have the values in 11/5/2012 12:00:00 AM format.

May be you need the truncate function for format compatibility.

hope that will help you

Regards,

Sushil

Re: Date variable not recognized by Oracle SQL

Mon, 05 Nov 2012 06:58:48 GMT

Hi,

your SQL is wrong. Try...

LET vFromDate = DayStart(Today());

BATCHLOG:
LOAD PK, Date1, Field2, Summation;
SQL
     SELECT PK, Date1, Field2, SUM(Field2) AS Summation
     FROM MY_TABLE
     WHERE Date1 >= to_date('$(vFromDate)', 'DD-MMM-YYYY')
     GROUP BY PK, Date1, Field2;

Best regards

Re: Date variable not recognized by Oracle SQL

Mon, 05 Nov 2012 07:13:40 GMT

I also tried below conditions. Its not working

Method 1 : Date1 >= To_Date('$(vFromDate)','DD-MMM-YYYY')

Method 2 : Date1 >= To_Date('$(vFromDate)','DD-MMM-YYYY h:mm:ss')

My source (Oracle) databse outputs SYSDATE as 05-Nov-2012 07:10:17

By the way, Date type of Date1 is "Date"

Re: Date variable not recognized by Oracle SQL

Mon, 05 Nov 2012 07:36:36 GMT

Hello,

it is a format issue. My fault. Oracle use MON as format code for e.g. 'Nov'

Date1 >= To_Date('$(vFromDate)','DD-MON-YYYY')

It is difficultly for me to reconstruct your problem, because I have other language codes.

On SQLPLUS you can use

SQL> select to_char(sysdate, 'DD-MON-YYYY') x from dual;

to try out the format codes