1

I am using a java GUI to input data into a SQL database. To do so, I am using a stored procedure in SQL and a dedicated method in java. When inputting the datetime, I enter it in YYYY-MM-DD HH:MM:SS, exactly as it is stored in the database, and I seem to keep getting the "Conversion failed when converting date and/or time from character string" as an SQL server exception.

My code is in Slovene, since it is my first language.The two key variables are translated for clarity, I hope it is enough.

this is my SQL stored procedure:

CREATE  PROCEDURE spNovDogodek
@Ime nvarchar(50),
@Ekipa nvarchar (30),
@Sezona nvarchar(30),
@StartTime datetime, 
@EndTime datetime
AS

INSERT INTO dbo.Dogodek (Dogodek.Ime, Dogodek.IDEkipe, Dogodek.IDSezone, Dogodek.StartTime, 
Dogodek.EndTime)
VALUES (@Ime, dbo.fnIDEkipe(@Ekipa), dbo.fnIDSezone(@Sezona), @StartTime, @EndTime)

I've tried using the nvarchar format for @StartTime and @EndTime (I know it is bad form, just wanted to see if that would help) since the input is a string, and then converting it with CONVERT(DATETIME, @whatever, 103), but it did not work, so I assume the SP is not the problem, which leads me to my java method:

    public static void novDogodek (String Ime, String Ekipa, String Sezona, String CasZacetka, 
    String CasKonca) {
        String userName = "***";
        String password = "*******";
        String serverName = "***.***.***.***";
        String dataBase = "DogodekVKlubu";
        String url = "jdbc:sqlserver://" + serverName + ":1433;DatabaseName=" + dataBase + 
    ";encrypt=true;trustServerCertificate=true";
    
    try  {
        // connecting to the database
        System.out.println("Povezovanje...");
        Connection conn = DriverManager.getConnection(url, userName, password);
    
        Statement stmt = conn.createStatement();

        CallableStatement cstmt1 = conn.prepareCall("{call dbo.spNovDogodek(?, ?, ?, ?, ?)}");
        
         cstmt1.setString("Ime", Ime);
         cstmt1.setString("Ekipa", Ekipa);
         cstmt1.setString("Sezona", Sezona);
         cstmt1.setTimestamp("StartTime", Timestamp.valueOf(StartTime));
         cstmt1.setTimestamp("EndTime", Timestamp.valueOf(EndTime));
         
        cstmt1.execute();
        
        conn.close();
    }
    catch (Exception e) {
        e.printStackTrace();
        
    }
    finally {
    }

I came to the current solution with the help of Using setDate in PreparedStatement and have tried a few different variations so far, I even tried using the Date.valueOf() while looking for the problem, which obviously throws an error at YYYY-MM-DD HH, but at least it converts.

What could be the issue? How can I troubleshoot this?

Thank you for your help.

Kalina
  • 21
  • 4
  • Try `new java.sql.Date(Starttime)`, if that still applies – Greg Sep 07 '22 at 18:12
  • When you were supplying the datetime values as strings and trying to convert on the SQL side, did you read the [CAST and CONVERT (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) documentation yet? The `103` style in `CONVERT(DATETIME, @whatever, 103)` was expecting the date portion to be `dd/mm/yyyy` format, not `yyyy-mm-dd` format. – AlwaysLearning Sep 07 '22 at 21:53
  • @AlwaysLearning I actually did not, I expected it to be in the same format as it is when I manually input it in DBeaver, which is yyyy-mm-dd. My current idea is to just separate the inputs for date and time (since date itself works for the yyyy-mm-dd format) and then use concat() to mash them together – Kalina Sep 09 '22 at 13:11

0 Answers0