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

Question

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.

remove the quotes. just use eventdtm >= @startDate (same thing for @endDate). No need to concatenate when you aren't using dynamic sql here. Also you only need a single @. This is a local variable. — S3S, Jun 05 '17 at 19:59
You also should use ANSI-92 style joins, they have been around for more than 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins And you should use meaningful aliases to avoid incredible amounts of pain. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 — Sean Lange, Jun 05 '17 at 20:06

score 2 · Accepted Answer · edited Jul 29 '20 at 18:30

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.

Removing the quotes and the concatenator worked. I tried variations on that, but never actually tried removing them completely. Thanks to both scsimon and SqlZim for your suggestions and help. I will also take a look at the other suggestions, as I am always willing to make my stuff better. — user3788019, Jun 05 '17 at 20:53

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

1 Answers1