Error doing Insert in BD SQL using JDBC

Asked

Viewed 92 times

0

I need to do an Insert using JDBC Connection, but inserting generates the error below.

com.microsoft.sqlserver.jdbc.Sqlserverexception: Conversion failed when Converting date and/or time from Character string.

Down here is my Insert.

insert into OFOBJOFICINALOCAL (CODCOLIGADA, IDOBJOF, CODLOCAL, DATAENTRADA, DATALANCAMENTO, DATASAIDA, CODUSUARIO, CODMOTIVOTRANSF,
RECCREATEDBY, RECCREATEDON, RECMODIFIEDBY, RECMODIFIEDON) values 
('1', 'CB-01', '2.0334', '07/10/2016 12:54', '2019-3-8T10:48:8',
'null', 'xxxx.xxxx', 'null', 'xxxx.xxxx', '2019-3-8T10:48:8',
'xxxx.xxxx', '2019-3-8T10:48:8')

Can anyone tell me how to fix it, because I’ve tried every way to change these dates but it won’t...

sql-server datetime jdbc

  • (1) The value 07/10/2016 12:54 means October 7 or July 10? (2) 'NULL' means you want to report no value or store the null string'?

  • You don’t need to put "solved" in the title. I know that on many websites it is a common practice, but here it is not necessary. You’ve already accepted an answer and that’s enough

2 answers

1


The error was caused by a given value for date & time columns: INPUT, DATE, DATE, DATE, RECCREATEDON, RECMODIFIEDON. Most likely in the seconds part of the time 10:48:8, although the string 'null' also be wrong.

The ISO 8601 format (yyyy-mm-ddThh:mm:ss) is independent of the SQL Server configuration for language and/or dateformat.

Try:

insert into OFOBJOFICINALOCAL (CODCOLIGADA, IDOBJOF, CODLOCAL, DATAENTRADA, DATALANCAMENTO, DATASAIDA, CODUSUARIO, CODMOTIVOTRANSF,
RECCREATEDBY, RECCREATEDON, RECMODIFIEDBY, RECMODIFIEDON) values 
('1', 'CB-01', '2.0334', '2016-10-07T12:54:00', '2019-03-08T10:48:08',
null, 'xxxx.xxxx', null, 'xxxx.xxxx', '2019-03-08T10:48:08',
'xxxx.xxxx', '2019-03-08T10:48:08')

  • Really, the column "null", should be inserted without quotation marks, thanks for the reply.

0

The error says:

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

Or in free translation:

Conversão falhou ao converter data e/ou hora de uma string

Depending on your configuration SQL Server will be considered the format AAAMMDD HH:mm:SS then you have two options:

  • Send date in this format:
INSERT INTO OFOBJOFICINALOCAL(CODCOLIGADA,
                              IDOBJOF,
                              CODLOCAL,
                              DATAENTRADA,
                              DATALANCAMENTO,
                              CODUSUARIO,
                              RECCREATEDBY,
                              RECCREATEDON,
                              RECMODIFIEDBY,
                              RECMODIFIEDON)
                       VALUES('1',
                              'CB-01',
                              '2.0334',
                              '20161007 12:54:00',
                              '20190308 10:48:08',
                              'xxxx.xxxx',
                              'xxxx.xxxx',
                              '20190308 10:48:08',
                              'xxxx.xxxx',
                              '20190308 10:48:08');
  • Or how you yourself reported using a JDBC you can use a PreparedStatement passing the variables as a parameter:
PreparedStatement ps = conexao.prepareStatement("INSERT INTO tabela(data) VALUES(?)");
ps.setDate(1, data);
ResultSet rs = ps.executeQuery();

Browser other questions tagged sql-server datetime jdbc

You are not signed in. Login or sign up in order to post.