sql server reporting services: Syntax error converting datetime from character string. -- sql server reporting services

You're in the

sql server reporting services

group:

Syntax error converting datetime from character string.

gvilinir NO[at]SPAM gmail.com
6/27/2007 1:07:29 AM

sql server reporting services:

Hello All,

I will Appreciate assistance
I getting the following error "Syntax error converting datetime from
character string."

when I run store procedure as follow:

exec Store_Procedure_Report5_test1 '06302007' (which the input
parameter is from type of datetime)

The Store procedue look like:

create procedure Store_Procedure_Report5
@date datetime, @style_from char, @style_to char,@color_from char,
@color_to char, @wh char
as
SELECT logfdetail.style,
logfdetail.color,
xqty1 = -1 * qty1,
xqty2 = -1 * qty2,
xqty3 = -1 * qty3,
xqty4 = -1 * qty4,
xqty5 = -1 * qty5,
xqty6 = -1 * qty6,
xqty7 = -1 * qty7,
xqty8 = -1 * qty8,
xqty9 = -1 * qty9,
xqty10 = -1 * qty10,
xqty11 = -1 * qty11,
xqty12 = -1 * qty12,

logfdetail.cutno,
logfdetail.logdate,
-- logfdetail.refno,
-- s1='',
-- s2='',
-- s3='',
-- s4='',
-- s5='',
-- s6='',
-- s7='',
-- s8='',
-- s9='',
-- s10='',
-- s11='',
-- s12='',

-- filename='test',
warehouse = isnull(logfdetail.warehouse,'00')
FROM logfdetail
WHERE ( logfdetail.location = 'HOUS' ) AND
( logfdetail.logdate between '1-1-1980' and dateadd(dd,
1,'@date') ) AND
( logfdetail.style between '@style_from' and '@style_to' )
AND
( logfdetail.color between '@color_from' and '@color_to' )
AND
( logfdetail.WAREHOUSE = '@wh')
UNION
SELECT invdetail.style,
invdetail.color,
invdetail.qty1,
invdetail.qty2,
invdetail.qty3,
invdetail.qty4,
invdetail.qty5,
invdetail.qty6,
invdetail.qty7,
invdetail.qty8,
invdetail.qty9,
invdetail.qty10,
invdetail.qty11,
invdetail.qty12,

invdetail.invoiceno,
invheader.invoicedate,
-- invheader.customer +'-'+ invheader.blname,
-- s1='',
-- s2='',
-- s3='',
-- s4='',
-- s5='',
-- s6='',
-- s7='',
-- s8='',
-- s9='',
-- s10='',
-- s11='',
-- s12='',

-- filename='test',
warehouse=isnull(invdetail.warehouse,'00')
FROM invdetail,
invheader
WHERE ( invdetail.invoiceno = invheader.invoiceno ) and
( ( invdetail.style between '@style_from' and '@style_to' )
AND
( invheader.invoicedate between '1-1-1980' and '@date' )
AND
( invdetail.color between '@color_from' and '@color_to' )AND
( invdetail.warehouse = '@wh'))
go

Thanks in Advance

Nir

Re: Syntax error converting datetime from character string.

EMartinez
6/27/2007 1:28:49 AM

[quoted text, click to view]

A couple of things that look like they could cause this error:
- '06302007' as the @date input into the stored procedure most likely
cannot be in this format to be implicitly converted to a datetime
value. It should be: '06/30/2007' or '06-30-2007'
- invheader.invoicedate between '1-1-1980' and '@date' should be
invheader.invoicedate between '1-1-1980' and @date (leave out the
single quotes around @date).
Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

Re: Syntax error converting datetime from character string.

gvilbis
6/28/2007 2:24:02 PM

Thanks Enrique its solved the problem

Nir

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn