on 11-04-2014 6:20 PM
Environment: PowerBuilder 10 Client. Database SQL Anywhere 9. Windows 7 OS.
Select Error: SQLSTATE = 07006
Sybase ODBC DRIVER SQL Anywhere Cannot Convert '1/1/200' to a timestamp
I'm getting this error from a PowerBuilder 10 app. when accessing the database ( sql anywhere 9).
It is happening on some workstations. Probably a DSN config on the workstation.
SQL Anywhere tries very hard to convert strings to dates, but sometimes it needs some help. The DATE_ORDER option defaults to 'YMD' to tell SQL Anywhere what the order of input date parts is if there's no other clue. In this case, you need to change DATE_ORDER either permanently or temporarily; for example:
BEGIN
DECLARE t TIMESTAMP;
SET TEMPORARY OPTION DATE_ORDER = 'MDY';
SET t = '1/1/2000';
SELECT t;
END;
t
'2000-01-01 00:00:00.000'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Rafael,
What is the date format on these machines, which have the error? You can examine it in the international settings of the registry under HKCU.
Are you getting this error when passing a date variable as a retrieval argument to datawindow, or in embedded sql? Could it be that you are using dynamic sql and this format change occurs in PB itself, and not in the DB?
Rafael and Arcady: This is NOT a client application or ODBC or Windows issue. The data is a string on the client side, not a date. The issue is on the SQL Anywhere side. Change the database option DATE_ORDER match the string layout. See the SQL Anywhere Help here.
Breck, let's not get ahead of ourselves here...
We do not know if this happens every time a date is referenced in the DB, it is not clear from the initial post.
If DB option is changed it might create a problem in other procedures where it does currently work.
This is why I asked my question. It could be a result of an unfortunate formatting of string in the PB code as well, I had my share of such problems over the years...
Refael,
First, this is the registry directory you should be looking at:
HKEY_CURRENT_USER\Control Panel\International
The variable in question is "sShortDate"
Second, if you have access to the PB source code, I suggest you make sure that the string formatting for dynamic sql is done like this: string(date_variable, 'yyyy-mm-dd'). It is highly unlikely to run into trouble, when this format is specified. Make sure to put '-' instead of '/' as date part separator.
This ought to fix it for good.
Refael,
You can display whatever and however you want. This is what EditMask datawindow object property is for. But when you compare dates in code or pass them to the DB as strings, the safest way to do it is to specify the most standard format imaginable.
If your date format is incompatible with SQL client, or even simply ambiguous (01/02/2014 , for example, can be Feb 1, or Jan 2), then specifying the format in code can really come in handy. When user looks at the screen he sees the date in his own Windows format (unless specified otherwise by EditMask).
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.