SQLExpress Stored Procedure in QT - Conversion failed when converting

Question

I seem to keep getting 'conversion' errors when calling and passing values into a stored procedure....

Im trying to write a dead simple procedure to insert data collected from a form here:

void orderform::on_btnSave_clicked()
{
    dbhandler().TestEntry(ui->edtOrderDate->date().toString());
};

this is the code in the dbhandler.cpp:

void dbhandler::TestEntry(QString PltfrmOrderDate)
{
    QSqlQuery query;
    query.prepare("{CALL [DSDB].[dbo].[Test](PltfrmOrderDate)}");
    query.bindValue("@PltfrmOrderDate", PltfrmOrderDate);
    if(!query.exec())
        qDebug() << query.lastError();
}

** Just a note, I know this is a date and that i can use QDate but that also returns a conversion error - Please see below **

The stored procedure:

USE [DSDB]
GO
/****** Object:  StoredProcedure [dbo].[Test]    Script Date: 1/20/2022 7:37:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test] 
    -- Add the parameters for the stored procedure here
    @PltfrmOrderDate                nvarchar
AS
BEGIN
   INSERT INTO [dbo].[OrdersTable]
    (
    PltfrmOrderDate
    )
    VALUES
    (
    @PltfrmOrderDate
    )
END

So, when i run my code like this I get the following error:

QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string." QSqlError("241", "QODBC3: Unable to execute statement", "[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.")

now to address the before sated QDate issue, That was how i had it innitially with the code as follows:

void orderform::on_btnSave_clicked()
{
    dbhandler().TestEntry(ui->edtOrderDate->date());
};

dbhandler.cpp:

void dbhandler::TestEntry(QDate PltfrmOrderDate)
{
    QSqlQuery query;
    query.prepare("{CALL [DSDB].[dbo].[Test](PltfrmOrderDate)}");
    query.bindValue("@PltfrmOrderDate", PltfrmOrderDate);
    if(!query.exec())
        qDebug() << query.lastError();
}

and finally the Stored Procedure:

USE [DSDB]
GO
/****** Object:  StoredProcedure [dbo].[Test]    Script Date: 1/20/2022 7:37:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test] 
    -- Add the parameters for the stored procedure here
    @PltfrmOrderDate                date
AS
BEGIN
   INSERT INTO [dbo].[OrdersTable]
    (
    PltfrmOrderDate
    )
    VALUES
    (
    @PltfrmOrderDate
    )
END

and this resulted in the following error:

QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to date." QSqlError("8114", "QODBC3: Unable to execute statement", "[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to date.")

What i dont understand is why is there conversions happening.... I'm passing a YYYY-MM-DD date into a YYYY-MM-DD date format, and QT documents seems to suggest that the format/data type is supported.

I ran into the same issue using Double form QT into Money in SQL, Int from QT into Int in SQL and even Bool in QT into Bit in SQL.

I'm sure its something small that I'm missing but its the first time I'm trying to use this and cant seem to find the issue!

Answer 1

So I have managed to resolve this by using QDateTime instead of QDate.

Here the input has changed from date to datetime

void orderform::on_btnSave_clicked()
{
    dbhandler().TestEntry(ui->edtOrderDate->dateTime());
};

Function parameter changed from QDate to QDateTime, as well as the ":" for the parameter in the query.prepare statement and the query.bindValue statement was missing

void dbhandler::TestEntry(QDateTime PltfrmOrderDate)
{
    QSqlQuery query;
    query.prepare("{CALL [DSDB].[dbo].[Test](:PltfrmOrderDate)}");
    query.bindValue(":PltfrmOrderDate", PltfrmOrderDate);
    if(!query.exec())
        qDebug() << query.lastError();
}

In the SP all that had to change was date to datetime

USE [DSDB]
GO
/****** Object:  StoredProcedure [dbo].[Test]    Script Date: 1/20/2022 7:37:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test] 
    -- Add the parameters for the stored procedure here
    @PltfrmOrderDate                datetime
AS
BEGIN
   INSERT INTO [dbo].[OrdersTable]
    (
    PltfrmOrderDate
    )
    VALUES
    (
    @PltfrmOrderDate
    )
END

Answer 2

1. For the first example, never declare nvarchar without length. As a stored procedure parameter, that becomes nvarchar(1), which means any string passed in gets truncated silently to a single character. An easy way to debug this yourself would have been to call the procedure manually with the same arguments and simply PRINT @PltfrmOrderDate; instead of INSERT. This will have printed 2 for any date this century.

2. Always use YYYYMMDD since YYYY-MM-DD is not a safe format in SQL Server - it can be interpreted as YYYY-DD-MM depending on your language settings and any data type it hits via implicit or explicit conversion. It may be worth showing us exactly how C++ is presenting PltfrmOrderDate to SQL Server.

3. Lots more background on using date and time data in SQL Server here: Dating Responsibly