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.

0

There are 0 best solutions below