CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2014
user3788019
user3788019

Reputation: 15

SQL Server Stored Procedure Conversion failed when converting date and/or time from character string

I know that this question has been asked a few times, but the suggestions don't seem to be working for me. I have an 83 line stored procedure that I need to call and pass a start and an end date into it. The procedure works great if I hard code the dates; however, when I try to pass the dates as variables I receive the error:

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

I am calling my stored procedure as follows: 

exec dbo.CreateReport @startDate = '10/01/2013', @endDate = '12/31/2013'

I am creating my stored procedure as follows: 

create procedure dbo.CreateReport 
    @startDate smalldatetime = NULL, 
    @endDate smalldatetime = NULL

The block of code where the error is occurring:

declare vp_laboraccount_timesheetitem_cursor cursor for 
            select a.laborleveldsc1,a.laborleveldsc2,a.laboracctid, sum(b.durationsecsqty)/3600 as totalhours
            from vp_laboraccount a, timesheetitem b
            where a.laboracctid=b.laboracctid and b.eventdtm >=''' + @@startDate + ''' and b.eventdtm <= ''' + @@endDate + ''' and employeeid = @personid
            group by a.laborleveldsc1,a.laborleveldsc2,a.laboracctid
            open vp_laboraccount_timesheetitem_cursor
            fetch next from vp_laboraccount_timesheetitem_cursor into @laborleveldsc1,@laborleveldsc2,@laboracctid,@totalhours
            while @@fetch_status=0
            begin
            --print results
                select @message = @personid + ',' + @personcstmdatatxt + ',' + @searchfullnm + ',' + @prdohh + ',' + @prjob1 + ',' + @laborleveldsc1 + ',' + @laborleveldsc2 + ',' + @laboracctid + ',' + @totalhours + ',' + @companhiredtm
                --print @message
                insert into dbo.tabResults (messages) Values(@message)
            fetch next from vp_laboraccount_timesheetitem_cursor into @laborleveldsc1,@laborleveldsc2,@laboracctid,@totalhours
            end
            close vp_laboraccount_timesheetitem_cursor
            deallocate vp_laboraccount_timesheetitem_cursor

The specific line where the error is occurring: 

where a.laboracctid=b.laboracctid and b.eventdtm >=''' + @@startDate + ''' and b.eventdtm <= ''' + @@endDate + ''' and employeeid = @personid

The two variables in question are the @@startDate and the @@endDate.

I have tried several things, including passing my variables in as varchar, as well as doing both a convert and a cast on the variables, but nothing seems to be working for me. I have tried both a single @ and a double @@ sign because those often give me problems. I have tried different date formats, but every time I get a variation of the same error message. If I hard code my date strings into the code it works as expected, and without errors.

I think that I am being very obtuse, and the solution will be obvious once somebody hits me over the head with a shovel, but after a week of trying various things I am ready to ask for help.

What am I doing wrong, and what do I have to do to fix this?

Thank you.

Upvotes: 1

Views: 2720

Answers (1)

SqlZim
SqlZim

Reputation: 38023

The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn] - Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

Use the single @ for your variable.

exec dbo.CreateReport @startDate='20131001', @endDate='20131231'

And your where clause should be like so:

where a.laboracctid=b.laboracctid
  and b.eventdtm >= @startDate
  and b.eventdtm <= @endDate
  and employeeid = @personid

Also, you should use proper joins instead of old-style joins and use meaningful aliases.

Upvotes: 2

Related Questions