August 2, 2010 at 11:37 pm
Hello,
I have parameters in store procedure called @date_from and @date_to. Both have data type varchar. Both of them are passed from front end.
Store Procedure is used to to generate reports between these dates.
My sql statement is as follows,
select * from tbl_SignatureOrder where TransactionDate between convert(datetime,@date_from) AND convert(datetime,@date_to)
and it gives me error : ' Conversion failed when converting datetime from character string. '
TransactionDate has data type of varchar(50).
Please help me out from this problem.
August 3, 2010 at 1:29 am
atikdesai (8/2/2010)
Hello,I have parameters in store procedure called @date_from and @date_to. Both have data type varchar.
TransactionDate has data type of varchar(50).
You have three fields that are used as date fields but are varchar, therefore the problem could be in any of these three. Try looking at the IsDate command to check the parameters and check TransactionDate for an invalid value.
BrainDonor.
August 3, 2010 at 3:25 am
First thing to say: storing date values as varchar is a really bad design idea. SQL Server came up with DATETIME and SMALLDATETIME for as reason!
Regarding the error: Other than simply wrong values e.g. 'greetings from bad design' you might also dealing with valid dates but the format doesn't match the DATEFORMAT setting.
For instance, '2010-06-20' is a valid format for a YMD setting but will fail if DATEFORMAT is changed to DMY.
The only format that will work independent of DATEFORMAT setting is '20100620' (read as YYYYMMDD). Any other format will call for trouble, e.g. '2010-05-06': is it May 6th or June 5th?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply