sql - Executing the stored procedure causes error -

i have stored procedure in want reportdate while executing.

i pass 1 parameter stored procedure executing it,. pass this

exec userreportdata '10-06-2016' 

but error:

the conversion of char data type datetime data type resulted in out-of-range datetime value.

this stored procedure:

alter procedure [dbo].[userreportdata]      @as_ondate datetime   begin      declare @reportdate datetime             --declare @opening int            select *      #temptable             (select               a.cuser_id, b.user_id, a.u_datetime reportdate                        inward_doc_tracking_trl a, user_mst b                       a.cuser_id = b.mkey              , convert(varchar(50), a.u_datetime, 103) = @as_ondate) x      declare cur_1 cursor          select cuser_id, user_id          #temptable      open cur_1      declare @cuser_id int     declare @user_id int      fetch next cur_1 @cuser_id, @user_id      while (@@fetch_status = 0)     begin         select convert(varchar(50), u_datetime, 103)          inward_doc_tracking_trl                                 convert(varchar(50), u_datetime, 103) = @as_ondate          update #temptable         set reportdate = @reportdate         cuser_id = @cuser_id           , user_id = @user_id          fetch next cur_1 @cuser_id, @user_id     end      close cur_1     deallocate cur_1      select * #temptable      drop table #temptable                            end 

kindly me know cause of error.

the various settings (language, date format) influence how datetime shown in sql server management studio - or how parsed when attempt convert string datetime.

there many formats supported sql server - see msdn books online on cast , convert. of formats dependent on settings have - therefore, these settings might work times - , not.

the way solve use (slightly adapted) iso-8601 date format supported sql server - format works always - regardless of sql server language , dateformat settings.

the iso-8601 format supported sql server comes in 2 flavors:

• yyyymmdd dates (no time portion); note here: no dashes!, that's important! yyyy-mm-dd not independent of dateformat settings in sql server , not work in situations!

or:

• yyyy-mm-ddthh:mm:ss dates , times - note here: format has dashes (but can omitted), , fixed t delimiter between date , time portion of datetime.

this valid sql server 2000 , newer.

if use sql server 2008 or newer , date datatype (only date - not datetime!), can indeed use yyyy-mm-dd format , work, too, settings in sql server.

don't ask me why whole topic tricky , confusing - that's way is. yyyymmdd format, should fine version of sql server , language , dateformat setting in sql server.

the recommendation sql server 2008 , newer use date if need date portion, , datetime2(n) when need both date , time. should try start phasing out datetime datatype if ever possible

so in concrete case - change how call stored procedure to:

exec userreportdata '20160610'    -- 10th of june, 2016 

or

exec userreportdata '20161006'    -- 6th of october, 2016  

depending on whether 6th october or 10th june of 2016 you're interested in ...