|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic SQL with Datetime variablesI 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 Week IS NOT NULL AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' AND clutr_nm LIKE ' + @Market + ' AND Convert(datetime, Week) >= ' + @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 Week IS NOT NULL AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' AND clutr_nm LIKE ' + @Market + ' AND Week >= ' + datepart(w,@startdate) is this the one you are looking for? what should be the result of the query -- Show quotebest 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 Week IS NOT NULL > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > AND clutr_nm LIKE ' + @Market + ' > AND Convert(datetime, Week) >= ' + @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) -- Show quotebest 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 Week IS NOT NULL > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > AND clutr_nm LIKE ' + @Market + ' > AND Week >= ' + 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 Week IS NOT NULL > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > AND clutr_nm LIKE ' + @Market + ' > > AND Convert(datetime, Week) >= ' + @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. 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. -- Show quoteLarry Menzin American Techsystems Corp. "Chandra" wrote: > The string concatination looks correct > > probable you are looking for: > > WHERE cat LIKE ' + @Product + ' AND Week IS NOT NULL > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > AND clutr_nm LIKE ' + @Market + ' > AND Week >= ' + 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 Week IS NOT NULL > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > AND clutr_nm LIKE ' + @Market + ' > > AND Convert(datetime, Week) >= ' + @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. 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 can´t be converted to a number) the query parser will punish you with an error. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Larry,
> > > AND Convert(datetime, Week) >= ' + @startdate + ' ' What data type is column [Week]?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 Show quote "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 Week IS NOT NULL > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > AND clutr_nm LIKE ' + @Market + ' > > AND Week >= ' + 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 Week IS NOT NULL > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > AND clutr_nm LIKE ' + @Market + ' > > > AND Convert(datetime, Week) >= ' + @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. 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? -- Show quoteLarry Menzin American Techsystems Corp. "Alejandro Mesa" wrote: > Larry, > > > > > AND Convert(datetime, Week) >= ' + @startdate + ' ' > > What data type is column [Week]? > > 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 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 Week IS NOT NULL > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > AND clutr_nm LIKE ' + @Market + ' > > > AND Week >= ' + 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 Week IS NOT NULL > > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > > AND clutr_nm LIKE ' + @Market + ' > > > > AND Convert(datetime, Week) >= ' + @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. > dynamic SQL. What is the performance hit from all these date to string Can you show the code?. I have not idea what conversions are you talking > conversions? about. AMB Show quote "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: > > > Larry, > > > > > > > AND Convert(datetime, Week) >= ' + @startdate + ' ' > > > > What data type is column [Week]? > > > > 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 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 Week IS NOT NULL > > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > > AND clutr_nm LIKE ' + @Market + ' > > > > AND Week >= ' + 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 Week IS NOT NULL > > > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > > > AND clutr_nm LIKE ' + @Market + ' > > > > > AND Convert(datetime, Week) >= ' + @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. Just expand this example to using all parameterized query and there are no
more headaches ;) use northwind go declare @i int declare @sql nvarchar(4000) declare @sd datetime declare @ed datetime set @sd = '1997/01/01' set @ed = '1997/12/31' set @sql = N'select @i = count(*) from dbo.orders where orderdate between @sd and @ed' exec sp_executesql @sql, N'@i int output,@sd datetime, @ed datetime', @i output,@sd,@ed print @i go Bojidar Alexandrov Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message gives me thenews:1C984BCA-610F-4A83-A5DE-295E28582942@microsoft.com... > Larry, > > > > > AND Convert(datetime, Week) >= ' + @startdate + ' ' > > What data type is column [Week]? > > 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 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 Week IS NOT NULL > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > AND clutr_nm LIKE ' + @Market + ' > > > AND Week >= ' + 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 Week IS NOT NULL > > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > > AND clutr_nm LIKE ' + @Market + ' > > > > AND Convert(datetime, Week) >= ' + @startdate + ' ' > > > > > > > > @startdate is the datetime variable. Using '' + @startdate + '' prints the > > > > literal variable @startdate, while using ''' + @startdate + ''' Show quote > > > > same conversion error. How do I solve this? > > > > > > > > -- > > > > Larry Menzin > > > > American Techsystems Corp. 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 --- Show quote "Larry Menzin" <LarryMen***@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 Week IS NOT NULL > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > AND clutr_nm LIKE ' + @Market + ' > AND Convert(datetime, Week) >= ' + @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. 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. -- Show quoteLarry 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" <LarryMen***@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 Week IS NOT NULL > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > AND clutr_nm LIKE ' + @Market + ' > > AND Convert(datetime, Week) >= ' + @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. > > > Larry,
First of all, what's with Convert (DateTime, Week) syntax? is that a typo? Did you mean DateAdd() ? or DatePart ?, cause Convert(DateTime, Week) is NOT a legal SQL Server FUnction... Convert requires three parameters. But anyway, It's NOT the @StartDate that is causing the problem... It's probably the [datetime] column in the convert function that is throwing the error... \/ AND Convert(DATETIME, Week) >= ' + @startdate + ' ' How is that column defined in the Table? Show quote "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" <LarryMen***@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 Week IS NOT NULL > > > AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' > > > AND clutr_nm LIKE ' + @Market + ' > > > AND Convert(datetime, Week) >= ' + @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. > > > > > > On Mon, 16 May 2005 07:49:22 -0700, CBretana wrote:
> First of all, what's with Convert (DateTime, Week) syntax? is that a typo? Hi Charly,(snip) It's actually correct syntax: the CONVERT function can be used without the style parameter: SELECT CONVERT(datetime, '20050101') In this case, week is the (pretty confusing) column name. SQL Server will allow this: SELECT CONVERT(datetime, Week) FROM (SELECT '20050101' AS Week) AS x Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Thanks, Hugo... Confusing is correct, I didn't even think of that...
Then the error he was experiencing, (Larry's long gone now, methinks) was probably from a string value in the "Week" column that could not be converted to a dateTime... Regards, Charly Show quote "Hugo Kornelis" wrote: > On Mon, 16 May 2005 07:49:22 -0700, CBretana wrote: > > > First of all, what's with Convert (DateTime, Week) syntax? is that a typo? > (snip) > > Hi Charly, > > It's actually correct syntax: the CONVERT function can be used without > the style parameter: > > SELECT CONVERT(datetime, '20050101') > > In this case, week is the (pretty confusing) column name. SQL Server > will allow this: > > SELECT CONVERT(datetime, Week) > FROM (SELECT '20050101' AS Week) AS x > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > On Mon, 16 May 2005 05:11:02 -0700, Larry Menzin wrote:
>I am having difficulty using dynamic SQL with datetime variables. A code Hi Larry,>snippet like this gives me an error about converting a string to datetime: > >... >WHERE cat LIKE ' + @Product + ' AND Week IS NOT NULL >AND RegionID LIKE ' + @Region + ' AND chnl_cd LIKE ' + @Channel + ' >AND clutr_nm LIKE ' + @Market + ' >AND Convert(datetime, Week) >= ' + @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? Why use dynamic SQL at all? WHERE cat LIKE @Product AND Week IS NOT NULL AND RegionID LIKE @RegionID -- Do you really need LIKE here?? -- AND RegionID = @RegionID -- This version might be lots quicker!! AND chnl_cd LIKE @Channel -- Same remark as above AND clutr_nm LIKE @Market -- Same remark as above AND CONVERT(datetime, Week) >= @startdate I also suggest that you rename the "Week" column. Though "week" is not a reserved keyword, it is displayed in a special color in Query Analyzer. This can easily lead to confusion. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics