Date format for execSProcCall

Hi,

I have troubles with a script: I need to call a stored procedure with a date parameter, but it doesn’t work. I always get the message “Error executing system.db.execSProcCall()” with “The conversion from UNKNOWN to DATE is unsupported”.
Note that I tried directly with the date format of ignition like system.date.now() and with a formated string value yyyY-MM-dd HH:mm:ss.SSS (the table has a datetime2 column).
When I run it in SQL Server Managment with the same values, it works fine.

From what I saw, I could run it with an EXEC in a runQuery, but it seems strange to me that registerInParam doesn’t work with a system.db.DATE.

Thanks.

Edit: didn’t manage to make it works with runQuery or runPrepQuery

Edit2: a coworker just find out it has to be system.db.VARCHAR with the formatted date to work.

Try with system.db.TIMESTAMP instead of system.db.DATE.

In MSSQL:

CREATE TABLE [dbo].[dates](
	[d] [date] NULL,
	[dt] [datetime] NULL,
	[dt2] [datetime2](7) NULL
);
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[dates_insert]
	@d DATE,
	@dt DATETIME,
	@dt2 DATETIME2
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO [dbo].[dates] ([d], [dt], [dt2]) VALUES (@d, @dt, @dt2);
END
GO

The code that fails:

from java.lang import Exception as JException

now = system.date.now()

try:
	call = system.db.createSProcCall('dbo.dates_insert')
	call.registerInParam('d', system.db.DATE, now)
	call.registerInParam('dt', system.db.DATE, now)
	call.registerInParam('dt2', system.db.DATE, now)
	
	system.db.execSProcCall(call)
except JException, e:
	print e.cause

# Output:
The conversion from UNKNOWN to DATE is unsupported.

The code that works:

from java.lang import Exception as JException

now = system.date.now()

try:
	call = system.db.createSProcCall('dbo.dates_insert')
	call.registerInParam('d', system.db.TIMESTAMP, now)
	call.registerInParam('dt', system.db.TIMESTAMP, now)
	call.registerInParam('dt2', system.db.TIMESTAMP, now)
	
	system.db.execSProcCall(call)
except JException, e:
	print e.cause

Result:

d          dt                      dt2
---------- ----------------------- ---------------------------
2018-05-18 2018-05-18 18:09:50.293 2018-05-18 18:09:50.2933333
1 Like

That works as well thanks !

Is there a way to know all the other possibilities for system.db ? I don’t see them on the manual or with ctrl + space, and this may be usefull.

In a script console, try:

print dir(system.db)

The available datatype constants are listed on the createSProcCall manual page.