SQL Anywhere Error -157: Cannot convert '08/10/09' to a timestamp

Question

This same question has been asked here: Getting Exception 'Cannot convert '08/10/09' to a timestamp' while connecting to Sybase Database with .Net

No answer has been provided and I am unable to post comments to that thread as I am a newer user so I am attempting to ask again.

I am attempting to build a .NET app to talk to a Sybase / ASE / SQL Anywhere database, using the information found at SAP here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sdk_12.5.1.adonet/html/adonet/Connecting_adodotnet_development.htm

With that all said, here is the code I am using:

private void MainWindow_Load(object sender, EventArgs e) {
        using (AseConnection con = new AseConnection("Provider=ASEOLEDB.1; Data Source=localhost; Port=2638; Database={correct db name}; Uid={correct username}; Pwd={correct password}; Charset=iso_1;")) {
            con.Open();
        }
    }

I get an exception thrown on con.Open():

"SQL Anywhere Error -157: Cannot convert '08/10/09' to a timestamp"

Stack Trace is as follows:

   at Sybase.Data.AseClient1.AseConnection.Open()
   at Sybase.Data.AseClient.AseConnection.Open()
   at ReportGenerator.MainWindow.MainWindow_Load(Object sender, EventArgs e) 
   in C:\Projects\DiCello\ReportGenerator\ReportGenerator\MainWindow.cs:line 21
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I have seen the following: https://archive.sap.com/discussions/thread/3646147, however this is not applicable as I am not attempting to run a query, I can not yet establish the connection to run the suggested query. The date 8/10/09 is not a date I have submitted anywhere. Also note this exception is on AseConnection.Open(), I have not submitted an ill formatted date, nor does the ill formatted date exist within the database.

So I figured it out but can't post the answer :( Please vote up my question so I can post the answer. — Dan Mayor, Jun 02 '17 at 16:00
Circling back to this, would love to post a more verbose answer but need some upvotes first. The issue is that the driver itself has a bug. You can connect using the generic ODBC driver / connector and won't have this issue. — Dan Mayor, Apr 22 '18 at 01:06
Hi, I have the same error. How you solved this ? Could you please share how to connect with ODBC ? — vivek v, Mar 06 '23 at 08:23

score 0 · Answer 1 · answered Jun 01 '17 at 15:45

0

It seems you try to apply a date (MMDDYYYY) datatype to a DateTime or Time datatype. These are essentially different datatypes containing different information.

Time:

Hours (hh)
Minutes (mm)
Seconds (ss)

Date:

Month (MM)
Days (DD)
Year (YYYY)

answered Jun 01 '17 at 15:45

ftp.x32

55
12

I don't understand as it's literally two lines of code. Creating the connection object with the connection string and calling the connect method. Is there something behind the scenes that automatically attempts to submit a date of some sort? – Dan Mayor Jun 01 '17 at 17:20

score 0 · Accepted Answer · answered Jun 06 '23 at 12:17

I finally have enough reputation to post this answer :D

The problem is the AseConnection library provided seems to have a bug, you will receive this error when opening the connection no matter what data is in your DB and without even trying to query yet. The answer that ended up working for me was to use the generic OdbcConnection:

Note: Connection string may vary

var connectionString = "Driver={SQL Anywhere 11}; Data Source = localhost,2638; DatabaseName=<db name>; Uid=<user>; Pwd=<password>; Charset=iso_1;"

using (OdbcConnection con = new OdbcConnection(connectionString)) 
{
    con.Open();
    using (OdbcCommand cmd = con.CreateCommand()) 
    {
        cmd.CommandText = "Your query here";
        using (OdbcDataReader reader = cmd.ExecuteReader()) 
        {
            while (reader.Read()) 
            {
                // Here reader is a Dictionary<string, object> for each row.  For example 
                // if you have a column called FirstName on the rows in your result set, 
                // you would access it's value like:
                var firstName = reader["FirstName"];
            }
        }
    }
}

SQL Anywhere Error -157: Cannot convert '08/10/09' to a timestamp

2 Answers2