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
BernardGBaileyAsked:
Who is Participating?
 
MikeOM_DBAConnect With a Mentor Commented:

It seems the Database Toour utlility is the culprit due to the fact that it will not recognize the DATE function for some of the databases.

Therefore you may want to use a parameter and supply the value at execution time:

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=:DATE

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and ufsale=getdate()

and ufsale>= convert(datetime, convert(varchar(10), getdate(), 120), 120)
and ufsale< dateadd(day, 1,convert(datetime, convert(varchar(10), getdate(), 120), 120))
0
 
BernardGBaileyAuthor Commented:
Hi AngelIII,

No joy with either,

Invalid use of keyword
Token:varchar(10)

I like the speed of response though :)

Cheers
Bernard
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
sathyagiriCommented:
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
0
 
BernardGBaileyAuthor Commented:
Hi sathyagiri,

>and ufsale=current_date

Local SQL thinks current_date is a field so it comes up "invalid fieldname"

Sorry,

Cheers
bernard
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see I overlooked that. Ignore my post, please
0
 
sathyagiriCommented:
Current_date is a function, so try current_date()
0
 
MikeOM_DBACommented:

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.


0
 
BernardGBaileyAuthor Commented:
Hi sathyagiri.

>and ufsale=current_date()

"capability not supported"

Cheers
Bernard
0
 
BernardGBaileyAuthor Commented:
Hi MikeOM,

>and ufsale=DATE()

"Invalid use of keyword"

I already tried this, sorry.

Cheers
Bernard
0
 
sathyagiriCommented:
What is the version of your LOCAL SQL?
0
 
BernardGBaileyAuthor Commented:
Hi Sathyagiri,

I'm using database tour.

Cheers
bernard
0
 
sathyagiriCommented:
Try using just DATE instead of DATE()
0
 
BernardGBaileyAuthor Commented:
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
0
 
suhinrasheedCommented:
TRY and ufsale=sysdate

or

check if above is not wrking

ufsale=(select sysdate from dual)
0
 
BernardGBaileyAuthor Commented:
Hi MikeOM_DBA

Thnaks this will do in the meantime until I can get Vitaly to fix the issue.

Cheers
Bernard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.