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

Dinesh Ambaliya

14y7.6k

in my stored procude I set two parameters,

@FromDate datetime = null,
@ToDate datetime = null,

and then write the WHERE clause of the SELECT statement like:

WHERE (a.AttendanceDate BETWEEN '+ CONVERT(DateTime,@FromDate) +' AND '+ CONVERT(DateTime, @ToDate) +' )';

when I hit F5 key then this stored procedure creates successfully.

But when I try to execute that procedure like:

EXEC @return_value = StoredSPName
@FromDate = N'2002-02-12',
@ToDate = N'2002-05-12',

it displayes error msg: "Conversion failed when converting date and/or time from character string.".

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Srinubabu Ravilla13y ago
In your stored procedure remove the Apostrophe ( ' ) and plus symbols.
Modify the statement in your stored procedure

Where (a.attendancedate BETWEEN '+ CONVERT(DateTime,@FromDate) +' AND

'+ CONVERT(DateTime, @ToDate) +' )';
To
Where (a.attendancedate BETWEEN CONVERT(DateTime,@FromDate) AND CONVERT(DateTime, @ToDate) );

Because while creating or modifying the stored procedure it is treating

'+ CONVERT(DateTime,@FromDate) +'
'+ CONVERT(DateTime, @ToDate) +'

both are strings.

So it will not throw any exception while creating or modifying the stored procedure.

Now execute your stored procedure

EXEC @return_value = StoredSPName
@FromDate = N'2002-02-12',
@ToDate = N'2002-05-12',
Thank you...
Jignesh Trivedi14y ago
hi,

I think this is problem with default datetime format with SQL. set datetime formate before you cast it.

Try..

declare @FromDate varchar(12) = null
set @FromDate = '2008-12-31'
SET DATEFORMAT ymd;
Print cast(@fromDate as datetime)

please refer..
http://msdn.microsoft.com/en-us/library/ms189491.aspx

hope this will help you.
Vulpes14y ago
Normally, I'd have suggested adding a 'style' parameter to the CONVERT function for the date format you're using but there doesn't appear to be one for yyyy-mm-dd unless 120 will work even though there's no time component:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

The alternative would be to present the date in a different format such as yyyymmdd (i.e. remove the dashes) which is style 112.