Why I obtain this error on a date field when I try to perform this query on SQL Server?

Question

I am finding some difficulties trying to perform this SQL query (on Microsoft SQL Server) into a Java appliction.

So I have this method that perform a simple select query:

public void insertOrUpdate_TIRConsolidatoPolizza(QS_TirPolizza qsTir) throws Exception {

    try{
        log.debug("PucManager.insertOrUpdate_TIRConsolidatoPolizza");

        // Reperisce i parametri della query dal parametro passato:
        String numeroPolizza = qsTir.getPolizzaid().toString();
        Long annoRiferimento = qsTir.getAnnoRiferimento();
        String dataRiferimentoNav = qsTir.getDataRiferimentoNav() != null ? "'"+qsTir.getDataRiferimentoNav()+"'" : null; 
        String timestamp = qsTir.getTimestamp() != null ? "'"+qsTir.getTimestamp()+"'" : null; 
        String sql = "select * from TirConsolidatoPolizza" +
                " where Polizzaid = " + numeroPolizza +
                " and DataRiferimentoNav = " + dataRiferimentoNav;

        log.debug("Query: " + sql);
        PreparedStatement ps = con.prepareStatement( sql );
        log.debug("Eseguo Query");
        ResultSet rs = ps.executeQuery();

        ...................................................
        ...................................................
        DO SOMETHING ELSE
        ...................................................
        ...................................................
}

The String sql variable value is (I see it printing it by log.debug()):

select * from TirConsolidatoPolizza where Polizzaid = 9999999999 and DataRiferimentoNav = 'Thu Jun 23 10:36:43 CEST 2016'

This query can't work because it seems that I have a problem with the DataRiferimentoNav value.

I obtain the following error message:

 10:42:41  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 241, SQL State: S0001]  Conversion failed when converting date and/or time from character string.

The DataRiferimentoNav (it is a datetime on the db table) value is taken from the qsTir.getDataRiferimentoNav() object field that is a String (I can't change it) representing a date and that was setted in this way elsewhere in my code:

qsTir.setDataRiferimentoNav(new Date().toString());

So, what is wrong? What am I missing? How can I solve this issue and correctly perform my query?

Answer 1

You need to proper format the date to be understood by the database:

Format formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String formatted = formatter.format(qsTir.getDataRiferimentoNav());

Ideally, you should avoid concatenating SQL as this can lead you to SQL injection vulnerabilities. Try to use parameterized queries to achieve your goal.