cancel
Showing results for 
Search instead for 
Did you mean: 

Error: Cannot Convert 1/1/2000 to a timestamp

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member188493
Contributor
0 Kudos

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'

Former Member
0 Kudos

This should be configuration setting in the ODBC client or some configuration file in the dbms server sql anywhere.

The error appears on some client machines.

former_member329524
Active Participant
0 Kudos

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?

former_member188493
Contributor
0 Kudos

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.

former_member329524
Active Participant
0 Kudos

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...

Former Member
0 Kudos

Hello Arcady,

I think it is a registry issue since this is only happening on a couple of workstations.

I'm not passing a date variable as a retrieval argument to the datawindow.  Yes. I'm using dynamic sql to modify the where clause.

Can you be more specific on the registry HKCU.

Thanks.

former_member329524
Active Participant
0 Kudos

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.

Former Member
0 Kudos

I checked on my PC and I have 'M/d/yyyy'.

Regarding  the dynamic sql. Why 'yyyy-mm-dd' and not 'mm/dd/yyyy' ??. The later is the default and what I want displayed.

former_member329524
Active Participant
0 Kudos

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).

former_member188493
Contributor
0 Kudos

> Breck, let's not get ahead of ourselves here...

whatever 

Former Member
0 Kudos

Sorry I can't be more specific but it is done in the ODBC server configuration.. You don't have to touch the code.

Thanks Breck and Arcady.

Answers (0)