SQL command returns different results running against a Great Plains table

I recently upgraded a Great Plains application from one version to another and the following SQL statement worked fine but after the upgrade the statement basically returns all the rows when run regardless of the date (SOP10100.actlship) or batch number (SOP10100.BACHNUMB) used as the filter. I'm running a SQL 2000 database.

Any ideas where to begin?

 SELECT SOP10100.SOPNUMBE, SOP10200.ITEMNMBR, SOP10200.QTYFULFI, SOP10200.QTYTOINV, SOP10100.SOPTYPE, SOP10100.BACHNUMB, SOP10100.ACTLSHIP
 FROM   KG.dbo.SOP10100 SOP10100 INNER JOIN KG.dbo.SOP10200 SOP10200 ON SOP10100.SOPNUMBE=SOP10200.SOPNUMBE
 WHERE  SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'} AND SOP10100.SOPTYPE=2 AND SOP10100.BACHNUMB<>'ship2/28/07' AND SOP10200.QTYFULFI>0
 ORDER BY SOP10100.SOPNUMBE
jdr0606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puranik_pCommented:
Is SOP10100.ACTLSHIP a date field?
if yes, this may be a problem...
SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'}

What exactly is ts?

change it to
SOP10100.ACTLSHIP<'2007-03-22 00:00:00'
0
jdr0606Author Commented:
The SOP10100.ACTLSHIP is a date field and as a follow-up what is the difference between using the SOP10100.ACTLSHIP<{ts '2007-03-22 00:00:00'} versus SOP10100.ACTLSHIP<'2007-03-22 00:00:00' for the same field type?  Can you clarify the SQL syntax definition for {ts '2007-03-22 00:00:00'}?

Thanks
0
puranik_pCommented:
In fact, I wanted to know the same thing...
what exactly is...{ts '2007-03-22 00:00:00'}?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PioneermfgCommented:
jdr0606;
  it looks like you wrote this in Access and not SQL.  What version of GP are you on now?  Also, I would verify the ACTLSHIP column to make sure the ACTLSHIP does not show 01/01/1900 (GP default date) if it does, i can give you a simple access query to update the ACTLSHIP from the SOP30200 table.
0
jdr0606Author Commented:
Pioneermfg, that doesn't surprise me, this is part of a script that I inherited from a previous developer from several years ago.
There are valid dates in the ACTLSHIP fields (i.e. 2007-01-31) but it sounds like you're saying the ...{ts '2007-03-22 00:00:00'} is actually Access syntax, what does it mean?

Thanks
0
PioneermfgCommented:
can you tell me what you are trying to accomplish with this query? based on what you posted, this will return anything prior to the date specifed.  what did this query return prior to upgrading GP?
0
PioneermfgCommented:
"ts" is the ODBC timestamp.  your query works with or without this piece (assuming you want all records prior to the date you have in your query).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.