Using ' on Stored Procedure Problem

[charmedcharmer]

Guys Im very very sorry. I just need a quick answer. Here is my stored procedure, Im having an error i think on the bold part.

CREATE PROCEDURE sp_DFListing_ByDate
@datetime1 datetime, @datetime2 datetime, @username varchar(15)
AS
 
declare @TableName varchar(30)
declare @CreateTable varchar(1000)
declare @InsertScript varchar(1000)
declare @QueryScript varchar(500)
 
select @TableName = '##tempDFListing_' + @username + ''
select @CreateTable = 'create table ' + @TableName + '
(charge_id uniqueidentifier, employee_nr char(10), caregiver_lname char(30), caregiver_fname char(30), caregiver_job_type char(30),
upi char(8), lname char(30), fname char(30), mname char(30), admission_date datetime, admission_type char(3), visit_type char(30),
item_code char(20), item_desc char(200), quantity int, uom char(20), unit_price money, total_amt money, charge_date datetime, invoiced_date datetime,
paid char(1))'
 
select @InsertScript = 'insert into ' + @TableName + ' select a.charge_id, a.employee_nr, a.caregiver_lname, a.caregiver_fname, a.caregiver_job_type, 
a.upi, a.lname, a.fname, a.mname, a.admission_date, a.admission_type, a.visit_type, b.item_code, b.item_desc, b.quantity, b.uom, b.unit_price, b.total_amt, b.charge_date, b.invoiced_date, b.paid
from charge_caregiver a, charge_detail b
where a.charge_id = b.charge_id and b.charge_date between  [B]' + @datetime1 + ' and ' + @datetime2 + '[/B] order by a.caregiver_lname, a.caregiver_fname, a.caregiver_job_type'
 
exec(@CreateTable)
exec(@InsertScript)
GO

The error I'm receiving is

Server: Msg 241, Level 16, State 1, Procedure sp_DFListing_ByDate, Line 17
Syntax error converting datetime from character string.

Thank you very much. Again, Im very sorry to ask this. I just need a quick answer. Thanks

[szlamany]

You need to use some kind of CONVERT style to make the DATETIME variables become string variables.

Try:

between  ' + Convert(varchar(10),@datetime1,101)
            + ' and ' + Convert(varchar(10),@datetime2,101) + ' order

style 101 is MM/DD/YYYY - this should work - otherwise check BOOKS ONLINE for other style values.