SQL Server conversion error

Question

I have the following SQL query to retrieve bills between 2 dates:

SELECT *
FROM BILLS
WHERE
    UPLOAD_DATE >= Convert(DATETIME, '2015-8-29 00:00:00', 120) 
    AND UPLOAD_DATE <= Convert(DATETIME, '2015-9-28 23:59:59', 120)

But when I execute this query, I get following error message;

Conversion failed when converting date and/or time from character String

I checked the format 120 and the syntax is yyyy-mm-dd hh-mi-ss (24h), so it's correct in my opinion.

Anyone who knows what is wrong?

I think `UPLOAD_DATE` is a varchar column and some values are not in correct format. try this statement for your confirmation : `select cast(UPLOAD_DATE as DATETIME) FROM Bills`. — Deep, Sep 28 '15 at 13:59
Look at the answers of this post http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i. you may get some idea. — M_Idrees, Sep 28 '15 at 14:09
Instead of passing strings and trying to convert them, used strongly-typed parameters. If you *have* to use date literals, use the unambiguous ISO format which doesn't need conversion (eg `UPLOAD_DATE between '2015-08-29T00:00:00' and '2015-09-28T23:59:59'`) — Panagiotis Kanavos, Sep 28 '15 at 14:23
If `UPLOAD_DATE` is a `date` column you can get rid of the time part. Either use `date`-typed parameters or the unseparated date format, ie `UPLOAD_DATE between '20150829' and '20150928'` — Panagiotis Kanavos, Sep 28 '15 at 14:27

Panagiotis Kanavos · Accepted Answer · 2015-09-28T14:43:54.880

The ODBC format uses two digits for month and date, ie 2015-08-29 00:00:00 instead of 2015-8-29 00:00:00.

It would be better though to use date-typed parameters. Otherwise, unambiguous date literals should be used, that don't require any conversions and aren't affected by the collation.

The full ISO 8601 format doesn't require conversions, eg: 2015-08-29T00:00:00. The unseparated date format also doesn't require conversion, eg 20150829.

If UPLOAD_DATE is a date column, there is no reason to specify the time part. The query could be written as follows:

declare @startDate date, @endDate date 
....

Select *
From BILLS
Where UPLOAD_DATE BETWEEN @startDate  AND @endDate

If the statement is part of a stored procedure, or is called by ADO.NET, this is the preferred way, eg:

var startDate=new DateTime(2015,8,29);
var endDate=startDate.AddMonths(1).AddDays(-1);

var myCmd=new SqlCommand("SELECT ....  Where UPLOAD_DATE BETWEEN @startDate  AND @endDate");

myCmd.Parameters.Add("@startDate",startDate);
myCmd.Parameters.Add("@endDate",endDate);
....

Using date literals the same statement can be:

Select *
From BILLS
Where UPLOAD_DATE BETWEEN '20150829'  AND '20150928'

The time part would be required only if UPLOAD_DATE is a datetime type. In this case though, it's easier to change the criteria to be less than the next date:

Select *
From BILLS
Where UPLOAD_DATE >= '20150829'  AND UPLOAD_DATE < '20150929'

SQL Server conversion error

1 Answers1