Great Plains 2013 - PM30300 table

I need to pull data from the PM30300 table but need to pull a certain range of dates due to the amount of data that we have.  The  docdate is the format 2015-8-15 00:00:00:000. How do I need to format the date in the SQL statement?
pstreAsked:
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.

Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

You can either use the BETWEEN keyword or comparison operators (>= and <=).

SELECT * FROM PM30300 WHERE DOCDATE BETWEEN '2015-08-15' AND '2015-08-31'

Let me know if that works for you.
0
pstreAuthor Commented:
Error message -- Conversion failed when converting date/and or time from character string. With the docdate format like I stated above, you can't just use docdate between
0
Victoria YudinOwner / Dynamics GP ConsultantCommented:
I don't have any problems executing the SQL statement in Steve's comment against our Dynamics GP data. Please see results below:
PM30300.png
Can you post a screenshot of your SQL Server Management Studio screen showing the query and the error message? Perhaps that will help us determine what the issue might be.

As a separate note, all GP dates are stored this way in SQL, as they are all datetime data types. There should be no issue referring to the date only in your searches, I do this all day every day.
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!

Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Can you also post the results of this statement from your server?

sp_help PM30300
0
pstreAuthor Commented:
Column_name      Type      Computed      Length      Prec      Scale      Nullable      TrimTrailingBlanks      FixedLenNullInSource      Collation
VENDORID      char      no      15                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
DOCDATE      datetime      no      8                            no      (n/a)      (n/a)      NULL
DATE1      datetime      no      8                            no      (n/a)      (n/a)      NULL
GLPOSTDT      datetime      no      8                            no      (n/a)      (n/a)      NULL
TIME1      datetime      no      8                            no      (n/a)      (n/a)      NULL
VCHRNMBR      char      no      21                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
DOCTYPE      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APFRDCNM      char      no      21                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
ApplyFromGLPostDate      datetime      no      8                            no      (n/a)      (n/a)      NULL
FROMCURR      char      no      15                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
APFRMAPLYAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APFRMDISCTAKEN      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APFRMDISCAVAIL      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APFRMWROFAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ActualApplyToAmount      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ActualDiscTakenAmount      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ActualDiscAvailTaken      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ActualWriteOffAmount      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APFRMEXRATE      numeric      no      9      19         7          no      (n/a)      (n/a)      NULL
APFRMDENRATE      numeric      no      9      19         7          no      (n/a)      (n/a)      NULL
APFRMRTCLCMETH      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APFRMMCTRXSTT      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APTVCHNM      char      no      21                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
APTODCTY      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APTODCNM      char      no      21                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
APTODCDT      datetime      no      8                            no      (n/a)      (n/a)      NULL
ApplyToGLPostDate      datetime      no      8                            no      (n/a)      (n/a)      NULL
CURNCYID      char      no      15                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
CURRNIDX      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APPLDAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
DISTKNAM      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
DISAVTKN      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
WROFAMNT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ORAPPAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ORDISTKN      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ORDATKN      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
ORWROFAM      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APTOEXRATE      numeric      no      9      19         7          no      (n/a)      (n/a)      NULL
APTODENRATE      numeric      no      9      19         7          no      (n/a)      (n/a)      NULL
APTORTCLCMETH      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
APTOMCTRXSTT      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
PPSAMDED      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
GSTDSAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
TAXDTLID      char      no      15                            no      yes      no      SQL_Latin1_General_CP1_CI_AS
POSTED      tinyint      no      1      3          0          no      (n/a)      (n/a)      NULL
TEN99AMNT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
RLGANLOS      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APYFRMRNDAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APYTORNDAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
APYTORNDDISC      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
OAPYFRMRNDAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
OAPYTORNDAMT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
OAPYTORNDDISC      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Gain_CreditCurrT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Loss_CreditCurrT      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Gain_DebitCurrTr      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Loss_DebitCurrTr      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Gain_DebitDiscAv      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Settled_Loss_DebitDiscAv      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
Revaluation_Status      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
Credit1099Amount      numeric      no      9      19         5          no      (n/a)      (n/a)      NULL
DEFTEN99TYPE      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
DEFTEN99BOXNUMBER      smallint      no      2      5          0          no      (n/a)      (n/a)      NULL
DEX_ROW_ID      int      no      4      10         0          no      (n/a)      (n/a)      NULL
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Very interesting situation.  The datetime data type looks correct, so I'm wondering if there is a setting issue (or an uncommon date setting) on the SQL Server.

Can you please run this statement and post the results?

DBCC USEROPTIONS

My speculation is that you have an atypical date format setting and SQL Server is unable to interpret the 'yyyy-mm-dd' format in the query.

Please try this statement to see if it works (adjust the date to a proper date range for your data):

SELECT * FROM PM30300 WHERE DOCDATE BETWEEN CAST('2017-08-15' AS datetime) AND CAST('2017-08-31' AS datetime)
1

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
pstreAuthor Commented:
This worked Steve -- THANKS...
0
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Would you mind running this statement and posting the results?  I'm curious to know if any of the settings contributed to the date formatting issue and would like to research it.

DBCC USEROPTIONS
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.