Posted by Erland Sommarskog on 11/09/07 11:49

(sunilkes@gmail.com) writes:
> Thanks, this gets me closer to what I want to achieve, but because of
> the @insertcmd being a varchar or nvarchar, I am having problems
> filtering the dates.
>
> CREATE PROCEDURE [dbo].[USP_Test]
> @strfinalint as varchar(1000),
> @startdate as datetime,
> @enddate as datetime
> as
>
> declare @insertcmd as nvarchar(2000)
>
>
> set @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.
> 4.0'',''Excel 8.0;Database=D:/MyDrive/' + @strfinalint + ';'',
> ''SELECT * FROM [Sheet1$]'') Select * from VwStaff WHERE S_Date
> Between '''+ @startdate + ''' AND '''+ @enddate + ''' '
>
> EXEC (@insertcmd)
> GO
>
> Am I doing something wrong? I tried using the @PARAMS but it does not
> seem to work,

What you do mean with "does not seem to work"? Did you get any error
message? Did you get unexpected results? Something else? I can't assist,
if I don't know what you are doing.

> so I tried the above, and now it tells me
>
> Syntax error converting datetime from character string.

That's because SQL Server has strict rules for data-type precedence, which
says that when two different data types meet, the one with lower
precedence gets converted to the higher. And varchar has lower priority
than datetime, so it's trying to convert the SQL string to datetime.
Which of course does work out.

You need to use convert to explicitly convert the dates to character, but
using parameterised commands is better in my opinion.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]