Date variable not recognized by Oracle SQL

[#1]

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';

[sushil353]

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

[#3]

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

[#4]

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"

[#5]

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