Dynamic SQL with Datetime variables

I am having difficulty using dynamic SQL with datetime variables. A code
snippet like this gives me an error about converting a string to datetime:
...
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND Convert(datetime, W) >= ' + @.startdate + ' '
@.startdate is the datetime variable. Using '' + @.startdate + '' prints the
literal variable @.startdate, while using ''' + @.startdate + ''' gives me the
same conversion error. How do I solve this?
Larry Menzin
American Techsystems Corp.The string concatination looks correct
probable you are looking for:
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND W >= ' + datepart(w,@.startdate)
is this the one you are looking for? what should be the result of the query
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Larry Menzin" wrote:

> I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints th
e
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me t
he
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Dont no whether you defined the datetime as ISO (then the implicit
conversion will to add a number to a string which wouldnt be that nice).
How did you dfine your Datetime variable ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints
> the
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me
> the
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Correction:
datepart(ww,@.startdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||Doesn't work.
When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'''
is used for strings, but what syntax is used for dates? I can't seem to find
anything that works.
Larry Menzin
American Techsystems Corp.
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||DECLARE @.startDate datetime
SET @.startDate = '4/1/2005' /* for testing */
I still can't get dynamic SQL to work using date variables embedded in the
SQL string.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> Don′t no whether you defined the datetime as ISO (then the implicit
> conversion will to add a number to a string which wouldn′t be that nice).
> How did you dfine your Datetime variable ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>
>|||Like mentioned in my previous post, the "Date" you are pasting in is in
some numeric format, so that SQL Server implicitly converts it to a number,
if you wanna add a number to a string (which cant be converted to a number)
the query parser will punish you with an error.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Larry,
What data type is column [W]?
Datetime values should be concatenated as characters values.
Example:
use northwind
go
declare @.i int
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
set @.sd = '19970101'
set @.ed = '19971231'
set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
dateadd(day, 1, @.ed), 112) + N''''
print @.sql
exec sp_executesql @.sql, N'@.i int output', @.i output
print @.i
go
AMB
exec
"Larry Menzin" wrote:
> Doesn't work.
> When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'
''
> is used for strings, but what syntax is used for dates? I can't seem to fi
nd
> anything that works.
>
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Chandra" wrote:
>|||Ok. I got it to work by converting datetime variables to character strings
and then comparing strings. Dates apparently cannot be used directly in
dynamic SQL. What is the performance hit from all these date to string
conversions?
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> Larry,
>
> What data type is column [W]?
> Datetime values should be concatenated as characters values.
> Example:
> use northwind
> go
> declare @.i int
> declare @.sql nvarchar(4000)
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '19970101'
> set @.ed = '19971231'
> set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
> convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
> dateadd(day, 1, @.ed), 112) + N''''
> print @.sql
> exec sp_executesql @.sql, N'@.i int output', @.i output
> print @.i
> go
>
> AMB
> exec
> "Larry Menzin" wrote:
>|||> dynamic SQL. What is the performance hit from all these date to string
> conversions?
Can you show the code?. I have not idea what conversions are you talking
about.
AMB
"Larry Menzin" wrote:
> Ok. I got it to work by converting datetime variables to character strings
> and then comparing strings. Dates apparently cannot be used directly in
> dynamic SQL. What is the performance hit from all these date to string
> conversions?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>

Dynamic sql problem

Hello: I am tearing my hair out over this.

I am using sql server 2005 and am getting the error "arithmetic overflow error converting nvarchar to numeric." The sp is below. It works fine when I do not include the numeric variable @.z and only insert the other 3 fields. The varbinary(MAX) is not the problem. The problem is syntax error mixing numbers and strings. How do I put the numeric @.z in the @.query string so I can insert a value into the FileID numeric field?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER off

ALTER PROCEDURE [dbo].[uspInsert_Blob] @.x as nvarchar(60), @.y as nchar(5), @.z as numeric(18, 0)

as

DECLARE @.QUERY VARCHAR(2000)

SET @.QUERY = "INSERT INTO myTable(FileID, FileName, FileType, Document)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " +@.z+ " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

EXEC (@.QUERY)

cast the variable to a nvarchar(18)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " + cast(@.z as nvarchar(18)) + " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

|||

Yes that works. I thank you so much.

When the exec query runs, does sql server recast it as numeric since the parameter and the field are numeric?

|||

Yes, it does an implicit conversion based on datatype conversion precedence:

You can see them listed here:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx

All lower precedence types get implicitly converted to the highest precedence type if it is possible.

Louis