Sybase dblib stored procedure

Question

I am using Sybase DB and using dblib interface( C++ Interface) to connect and pass commands to Sybase DB. I have one stored procedure added to Sybase DB. Below is the signature of stored procedure:

create procedure process_write @id varchar(35), @pTime datetime,@status tinyint

and I am calling this stored procedure with below :

process_write '000000100', '22/9/2022 10:18:37', 1

Now when I run my code I do not see error on console and the stored procedure executes successfully, But when I run this in isql command prompt, I get below error:

Msg 247, Level 16, State 1: Server 'ABCXYZ', Procedure 'process_write': Arithmetic overflow during implicit conversion of VARCHAR value '22/9/2022 10:18:37' to a DATETIME field . (return status = -6)

I am not able to figure out how this is working in code but failing in isql console?

Answer 1

tl;dr

ASE defaults to processing strings of the format X/Y/Z as M/D/Y and this likely explains why your isql session is generating an error (ie, your isql session is running with mdy as its default dateformat). My guess is that somewhere in your dblib/C++ code you've either modified the dateformat or the language used by the dblib/C++ session which in turn insures your proc call works (ie, no conversion errors are generated).

I don't work with dblib/C++ so I don't know if you need to modify a db connection attribute or if you just issue a T-SQL command upon successful connection, eg:

set dateformat 'dmy'

---

In Sybase ASE when processing strings as dates it's necessary to tell ASE the ordering of the date components in strings like X/Y/Z.

From a strictly T-SQL point of view there are two set options that can tell ASE how to interpret X/Y/Z as a date:

• set dateformat <format> - where <format> is one of 'mdy', 'myd', 'ymd', 'ydm', 'dmy' and 'dym'; default is 'mdy'
• set languange <language> - where <language> is going to be based on what languages you've loaded into ASE; default is 'us_english' which causes the dateformat to default to 'mdy' [I don't have details on if/how individual languages may modify the dateformat so you would need to run some tests in your environment]

NOTES:

• see T-SQL Users Guide: Date Formats and Reference Manual: Commands: set for more details.
• to see the current dateformat in use for your session: select get_appcontext('SYS_SESSION','dateformat')

---

Demonstrating the use of set dateformat with a stored proc:

create proc testp
@pTime datetime
as
select @pTime
go

-------------

select get_appcontext('SYS_SESSION','dateformat')
go

 ---
 mdy      <<<--- ASE default

exec testp '22/9/2022 10:18:37'
go

Msg 247, Level 16, State 1:
Server 'ASE400', Procedure 'testp':
Arithmetic overflow during implicit conversion of VARCHAR value '22/9/2022 10:18:37' to a DATETIME field .

---------------- 

set dateformat 'dmy'
go

select get_appcontext('SYS_SESSION','dateformat')
go

 ---
 dmy  

exec testp '22/9/2022 10:18:37'
go

 -------------------
 Sep 22 2022 10:18AM