CODEWITHSUNDEEP
sqlsql-server
Prometheus
Prometheus

Reputation: 33635

SQL: out-of-range value

The following query gives this error

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

The field timedate looks like this 13/11/2012 uk format in the database.

anyone know how to solve this issue. 

        SELECT
         PRODID, ITEMDES, QTY, StockCode,shipName, shipCompany, shipAddress1, shipAddress2, shipAddress3,shipPostCode,shipcity,shipCountry,shipCounty,customerMessage
         FROM orderedItems oi
        left join orders o on oi.order_id = o.order_id

            where( CONVERT(nvarchar(30),timedate,120) >= 
            <cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(form.fromDate, "yyyy-mm-dd")# 00:00:00">
            AND CONVERT(nvarchar(30),timedate,120) <= 
            <cfqueryparam cfsqltype="cf_sql_date" value="#LSDateFormat(FORM.toDate, "yyyy-mm-dd")# 23:59:59">
            )

            Group by PRODID,ITEMDES,QTY, StockCode,shipName, shipCompany, shipAddress1, shipAddress2, shipAddress3,shipPostCode,shipcity,shipCountry,shipCounty,customerMessage

            ORDER BY PRODID

Upvotes: 0

Views: 2978

Answers (1)

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

I think that sql-server might be interpreting your date as if it was in american format, that is mm/dd/YYYY, and obviously there is no 13th month, hence the out-of-range error.

You'll probably be better suited saving the date using the proper field type.

Upvotes: 1

Related Questions