Unable to run SQL query on GP data with dates - Conversion of a varchar to a datetime error

By Steve Endow

An interesting Dynamics GP query question came up on Experts Exchange.  The user was asking how to select transactions for a given date range from the PM30300 table.  Pretty straightforward--I recommended this query as a start:

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '2017-01-01' AND '2017-03-31'

The user tried it, but said that he received the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Puzzling.  I asked him to run this sp_help statement to verify that the GP PM30300 table did have the correct datetime data type for the DOCDATE field.

EXEC sp_help PM30300

The table looked fine and the DOCDATE field was set as a datetime data type.

DOCDATE datetime

Very odd.  So the table looks okay.  The query looks okay.  And I assumed the data in the DOCDATE field was okay.

So why would a query filtering dates give a data type error?

I looked at the error again.  Conversion of a varchar to a date time.  When we use a date filter of '2017-01-01', that is a string, and SQL Server is implicitly converting that to a datetime data type.

So that means that for some reason, when the user sent in the value of '2017-01-01', SQL Server failed to convert it to a datetime.  But that date format obviously works for me, so why wasn't it working for him?

Enter the mess called regional settings.  Start by running this statement to view the SQL User Options settings.

DBCC USEROPTIONS

In the results, look at the language and deateformat values.

My settings showed a language of us_english, and a dateformat of mdy.  So with this setting, SQL Server is able to apparently implicitly convert the '2015-07-01' date value to the mdy date format.

But then I ran this statement against the sa login:

ALTER LOGIN [sa] WITH DEFAULT_LANGUAGE = [British];

This change only takes effect the next time you connect, so you have to close your query and open a new query.  When I do that, here is what I see.

In addition to changing the language, the dateformat changes.

It is possible to change the dateformat value directly, but that change will only persist for the active connection. Once the connection is closed and recreated, the setting will default back to the user options value.

So now that I have set my language to British, which has a dateformat of dmy, what happens when I run my simple query with a date filter?

There ya go.

So it would seem that the user has a language setting other than us_english for their SQL Server login, and that language in turn as a dateformat other than mdy.

The simple fix would be to just run this statement for whatever login is having the issue:

ALTER LOGIN [sa] WITH DEFAULT_LANGUAGE = [us_english];

This sets the language to us_english and the dateformat to mdy.  Once that setting is changed and you reconnect to SQL, you should be able to query with a date format of '2017-01-01'.

The potential downside is that there may be other applications that rely on the language value, and dateformat, that may break if you change to us_english.  If you are unable to change the default language setting, you have two options.

You could potentially change the date format you can use in your queries:

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '15-01-2017' AND '31-03-2017'

The problem with this is if you have users with different language settings.  If a us_english user tries to run this query, it will fail with the same 'conversion of varchar' error.

Another option is to explicitly cast your date values to datetime:

SELECT * FROM TWO01..PM30300 WHERE DOCDATE BETWEEN CAST('15-01-2017' AS datetime) AND CAST('31-03-2017' AS datetime)

But in my test, even this does not work for users that have different language and dateformat settings.

As a last resort, it looks like this option would work:

SET DATEFORMAT mdy

This statement would have to be run before every query, as it only persists during the connection.  But it ensures that you are using a known date format for all queries.

This is the first time I've run into this, but since most of my customers are in the US, it isn't too surprising.  

Consultants in other countries may run into this regularly.

Now if only the rest of the world could see the error of their ways and finally start using mdy, inches, ounces, pounds, miles per hour, etc.


9/10/2015 UPDATE: David Musgrave offered some sage wisdom in the comments. He explained that if you use the date format of YYYYMMDD in SQL queries, you avoid these issues.  Sure enough, this query works regardless of the Default Language setting.

SELECT * FROM TWO..PM30300 WHERE DOCDATE BETWEEN '20170115' AND '20170331'

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com