Where Conditions

[#1]

Hiya,

Can anyone tell me where I'm going wrong at the end of my query I am using the following where clause:

Where usage_date > (date(today()-31,'DD/MM/YYYY'))

and usage_date < date(today(),'DD/MM/YYYY')

and Not  isnull(usage_date)

It is failing on the second line, the error it is giving me is field "and" not recognised :S ???!!!

[#2]

Grr ! lol just retyped the whole thing and is failing at that point. Makes no sense !

[sasiparupudi1]

May be try

QualifY *;

Unqualify clarapw_id;

CARE:

Load

DateTime,

clarapw_id,

usage_date,

bytes_downloaded,

bytes_uploaded

Resident clarapw_usage

Where clarapw_usage.usage_date >  date(today()-31,'DD/MM/YYYY')

and clarapw_usage.usage_date < date(today(),'DD/MM/YYYY')

and Not  isnull(clarapw_usage.usage_date)

Order by clarapw_usage.usage_date asc;

[Kushal_Chawda]

try

Where (date(date#(usage_date,'DD/MM/YYYY'),'DD/MM/YYYY') >  date(today()-31,'DD/MM/YYYY')

and date(date#(usage_date,'DD/MM/YYYY'),'DD/MM/YYYY') < date(today(),'DD/MM/YYYY'))

and Not  isnull(usage_date)

[marcus_malinow]

Sarah,

a couple of things I'd try:

first off I'd change the calculation of the dates so that they are enclosed in a '$(= )', so that they are evaluated rather than passed through to your database. Also note that I've enclosed the entire string in quotes.

Secondly I'd change the format of the date to YYYY-MM-DD as this can not be misinterpreted based on your regional settings.

So this is the where clause:

Where usage_date > '($=date(today()-31,'YYYY-MM-DD'))'

and usage_date < '($=date(today(),'YYYY-MM-DD'))'

and Not  isnull(usage_date)

[jonathandienst]

As long as the field usage_date is coming in as a proper QV date, using the Date() function is entirely unnecessary. The Not Isnull() will be done implicitly by the other comparisons, so it is also unnecessary. So the condition becomes:

LOAD ...

FROM ...

Where usage_date > today()-31 and usage_date < today();

If usage_date is a string, you need the date conversion function Date#() (NOT Date()) to convert to a date from the comparison:

Where Date#(usage_date, '<format>') > today()-31 and Date#(usage_date, '<format>') < today();

(<format> is the date format of the string usage_date).

These comparisons are numeric, so the regional date format is no longer an issue.

[marcus_malinow]

my bad, I thought this was a database query rather than a resident load.

[#8]

Thanks guys for your help it turned out to be a variable setting it was referencing further up that was causing the script to fail even though it was giving me the error here.  So annoying ! thanks sorry for wasting your time