BernardGBailey
asked on
Local SQL select for date=today
Hi team,
I'm trying to limit my extract from two tables to those records where ufsale = today
SELECT (trim(both " " from key1)+"/0 "+ g1.company) as receivercompany,
ulocname as locationname,
g1.zip as costcentre,
g1.contact as receiverattn,g1.phone1 as receiverph,
g1.address1,
g1.address2,
g1.city as address3
from Contact1 g1, contact2 g2
where g1.accountno =g2.accountno
and ufsale=getdate()
Using Access or SQL Server this would work fine, but for Local SQL what is the syntax for where ufsale=today?
Urgent help required for a simple question, note it is "Local SQL" as in Paradox,xBase
All help is appreciated.
Cheers
Bernard
I'm trying to limit my extract from two tables to those records where ufsale = today
SELECT (trim(both " " from key1)+"/0 "+ g1.company) as receivercompany,
ulocname as locationname,
g1.zip as costcentre,
g1.contact as receiverattn,g1.phone1 as receiverph,
g1.address1,
g1.address2,
g1.city as address3
from Contact1 g1, contact2 g2
where g1.accountno =g2.accountno
and ufsale=getdate()
Using Access or SQL Server this would work fine, but for Local SQL what is the syntax for where ufsale=today?
Urgent help required for a simple question, note it is "Local SQL" as in Paradox,xBase
All help is appreciated.
Cheers
Bernard
ASKER
Hi AngelIII,
No joy with either,
Invalid use of keyword
Token:varchar(10)
I like the speed of response though :)
Cheers
Bernard
No joy with either,
Invalid use of keyword
Token:varchar(10)
I like the speed of response though :)
Cheers
Bernard
SELECT (trim(both " " from key1)+"/0 "+ g1.company) as receivercompany,
ulocname as locationname,
g1.zip as costcentre,
g1.contact as receiverattn,g1.phone1 as receiverph,
g1.address1,
g1.address2,
g1.city as address3
from Contact1 g1, contact2 g2
where g1.accountno =g2.accountno
and ufsale=current_date
ulocname as locationname,
g1.zip as costcentre,
g1.contact as receiverattn,g1.phone1 as receiverph,
g1.address1,
g1.address2,
g1.city as address3
from Contact1 g1, contact2 g2
where g1.accountno =g2.accountno
and ufsale=current_date
ASKER
Hi sathyagiri,
>and ufsale=current_date
Local SQL thinks current_date is a field so it comes up "invalid fieldname"
Sorry,
Cheers
bernard
>and ufsale=current_date
Local SQL thinks current_date is a field so it comes up "invalid fieldname"
Sorry,
Cheers
bernard
I see I overlooked that. Ignore my post, please
Current_date is a function, so try current_date()
For "Local SQL", the date function may be different (on Paradox, xBase and FoxPro it's DATE()).
If you want to code "generic" SQL, you would need to create a 'getdate()' function for each those DB's that have another name for this function.
ASKER
Hi sathyagiri.
>and ufsale=current_date()
"capability not supported"
Cheers
Bernard
>and ufsale=current_date()
"capability not supported"
Cheers
Bernard
ASKER
Hi MikeOM,
>and ufsale=DATE()
"Invalid use of keyword"
I already tried this, sorry.
Cheers
Bernard
>and ufsale=DATE()
"Invalid use of keyword"
I already tried this, sorry.
Cheers
Bernard
What is the version of your LOCAL SQL?
ASKER
Hi Sathyagiri,
I'm using database tour.
Cheers
bernard
I'm using database tour.
Cheers
bernard
Try using just DATE instead of DATE()
ASKER
Hi Sathyagiri,
>and ufsale=DATE
"Invalid use of keyword
Token:DATE"
I guess this means there is another format of DATE we haven't arrived at yet.
Cheers
Bernard
>and ufsale=DATE
"Invalid use of keyword
Token:DATE"
I guess this means there is another format of DATE we haven't arrived at yet.
Cheers
Bernard
TRY and ufsale=sysdate
or
check if above is not wrking
ufsale=(select sysdate from dual)
or
check if above is not wrking
ufsale=(select sysdate from dual)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi MikeOM_DBA
Thnaks this will do in the meantime until I can get Vitaly to fix the issue.
Cheers
Bernard
Thnaks this will do in the meantime until I can get Vitaly to fix the issue.
Cheers
Bernard
and ufsale>= convert(datetime, convert(varchar(10), getdate(), 120), 120)
and ufsale< dateadd(day, 1,convert(datetime, convert(varchar(10), getdate(), 120), 120))