datetime and stored proedures
-
hi everybody, I have a small proplem with my stored procedure that looks like this create procedure MonthlyBill @startDate datetime, @endDate datetime as select * from Bills where Bills.date >=@startDate and Bills.date<@endDate the problem I have is that when I execute this query using the following command: execute MonthlyBill '01/10/2005','30/10/2005' I get the following error msg: Error converting data type varchar to datetime. both @endDate and @startDate are declared as datetime variables.From where does come the problem?I anybody knows and would like to help please do!!!I'm thankfull The will to learn
-
hi everybody, I have a small proplem with my stored procedure that looks like this create procedure MonthlyBill @startDate datetime, @endDate datetime as select * from Bills where Bills.date >=@startDate and Bills.date<@endDate the problem I have is that when I execute this query using the following command: execute MonthlyBill '01/10/2005','30/10/2005' I get the following error msg: Error converting data type varchar to datetime. both @endDate and @startDate are declared as datetime variables.From where does come the problem?I anybody knows and would like to help please do!!!I'm thankfull The will to learn
You need to convert from the varchar type to the datetime type. This might help.
execute MonthlyBill to_date('01/10/2005','DD/MM/YYYY') to_date('30/10/2005','DD/MM/YYYY')
Hopefully whatever DB platform you are using, there is a function, 'to_date' that will translate a varchar to a datetime. :) Chris Meech I am Canadian. [heard in a local bar] Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer] Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
-
hi everybody, I have a small proplem with my stored procedure that looks like this create procedure MonthlyBill @startDate datetime, @endDate datetime as select * from Bills where Bills.date >=@startDate and Bills.date<@endDate the problem I have is that when I execute this query using the following command: execute MonthlyBill '01/10/2005','30/10/2005' I get the following error msg: Error converting data type varchar to datetime. both @endDate and @startDate are declared as datetime variables.From where does come the problem?I anybody knows and would like to help please do!!!I'm thankfull The will to learn
Look at this example stored procedure:
create procedure MonthlyBill @startDate datetime, @endDate datetime as select * from Bills where Bills.date >=convert(datetime,@startDate, 102) and Bills.date<convert(datetime,@endDate, 102)then you can call the stored procedure like this:exec MonthlyBill '2005/10/1', '2005/10/30'I actually use this source[^] to use stored procedures and I forget about type cast problems. Take a look at it, it's really good. daniero -
hi everybody, I have a small proplem with my stored procedure that looks like this create procedure MonthlyBill @startDate datetime, @endDate datetime as select * from Bills where Bills.date >=@startDate and Bills.date<@endDate the problem I have is that when I execute this query using the following command: execute MonthlyBill '01/10/2005','30/10/2005' I get the following error msg: Error converting data type varchar to datetime. both @endDate and @startDate are declared as datetime variables.From where does come the problem?I anybody knows and would like to help please do!!!I'm thankfull The will to learn
One note I would like to add to the other respondents. I am not a big fan of using operators when comparing datetimes. If, for example, one of your parameters is using GetDate() (sql server) the <,> etc operators check the time. This can lead to very undesirable results! If you are not interested in the time, it is better to use DATEDIFF(dd,date1,date2) < 0. You'll find yourself chasing down less problems this way.