SQL Load

[danielnevitt]

Hi,

I'm trying to write a SQL load statement to restrict my data by TIMEMODIFIED greater than or equal to today.

My first issue is that my load statement (see below) doesn't seem to work and I have no idea why?

//-------- Start Multiple Select Statements ------

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE TIMEMODIFIED = 21/10/2013;
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

//-------- End Multiple Select Statements ------

Secondly is it possible to have the date (in this case 21/10/2013) as a variable that equals today?

Any help will be really appreciated.

Thanks,

Daniel

[javier_florian]

Restrict in SQL DB:

Table_A:
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE

WHERE CONVERT(DATETIME, DATA_FIELD) <= CONVERT(DATETIME, 'DD/MM/YYYY');

[Anonymous]

Daniel, you can try this:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED' = '21/10/2013';                         // notice single quites
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

For today:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED) = num(today());
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

Reagrds,

Michael

[MayilVahanan]

HI

Try like this

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE Date(TIMEMODIFIED,'DD/MM/YYYY') >=  MakeDate(2013,10,21);;

[danielnevitt]

Hi Michael,

Thank you for the reply.  Unfortunately when I try to run that script I receive the following message:

ODBC read failed

SQL SELECT *

FROM "IXOMEX_OWNER".ZZQUOTEPRICE

Do you have any idea what the issue might be?

Thanks,

Daniel

[javier_florian]

Do you check you odbc connection? And do you have odbc line in your script?

[danielnevitt]

Hi Javier,

The ODBC connection is working ok (I checked without the LOAD and WHERE statement).  The ODBC connect line is also in the statement above the start multiple statements line.

Regards,

Daniel

[Anonymous]

The error message may be misleading.  I see at least one place to fix.  In this load:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED' = num(today());SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

There is field TIMEMODIFIED twice - in * and by itself.  Try to change a little:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DateModified     // different name
WHERE floor(TIMEMODIFIED' = num(today());
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

[danielnevitt]

Hi Michael,

Thanks again for the reply.  Unfortunately the script now returns 0 results.

The TIMEMODIFIED column is TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]', so I'm not sure whether this is what's causing the issue?

Regards,

Daniel

[Anonymous]

I take care about time format with floor() function.  The floor(TIMEMODIFIED) returns integer, that is the time part (hh:mm:ss [.fff]) is removed and the date part stays.
Can you verify in the database directly if there are records with today's date?

And, I see my mistyping, it should be:

WHERE floor(TIMEMODIFIED) = num(today());