Search code examples
javasql-serverjdbcsmalldatetime

JDBC encoding to sql server smalldatetime

I'm trying to execute a stored proc where one of the parameters is of type smalldatetime. In the past I've always encoded datetime2 and datetime fields as strings and that worked fine. I'm using the Microsoft JDBC Driver

I am getting this error when trying to use a string/nvarchar for the smalldatetime parameter.

Error converting data type nvarchar to smalldatetime. Query: EXEC dg.FooBar ?, ?, ?, ?, ? Parameters: [[123, 1, 2099-01-01, 1, DG TEST]]

Looking at the microsoft documentation it suggests using the java.sql.Timestamp class. Changing my java code to Timestamp.valueOf(LocalDate.of(2099, 1, 1).atStartOfDay()) I get a similar error.

Error converting data type datetime2 to smalldatetime. Query: EXEC dg.FooBar ?, ?, ?, ?, ? Parameters: [[123, 1, 2099-01-01 00:00:00.0, 1, DG TEST]]

Any help is appreciated.

Solution

  • The date range supported by smalldatetime is 1900-01-01 through 2079-06-06

    https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql

    You are attempting to pass 2099-01-01 00:00:00.0, which is out of range.