Date conversion ?

Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"

Please help me !

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 120) +')'

EXEC (@.keyssql)What parameters are you using to execute the stored procedure ?|||@.datainceput DATETIME
@.datasfirsit DATETIME

datainceput = 01.01.2004
datasfirsit = 15.01.2004

I want to make a SQL_String something like this :

SQL_String = 'SELECT * FROM TABLE WHERE ' .... date condition

EXECUTE (SQL_String)

All this inside a stored procedure

Sorry for my english|||I have two option

1. Sp_1

SELECT * FROM TABLE WHERE ............

Is ok, work

2. Sp_2

DECLARE @.keyssql AS varchar(8000)
SET @.keyssql ='SELECT * FROM TABLE WHERE' + 'Condition'

EXECUTE (@.keyssql) -- This line is inside at the same stored procedure.

This stored procedure Sp_2 don`t work|||Enjoy ...

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 104) +')'

EXEC (@.keyssql)|||ALTER PROCEDURE SP_2
As
set nocount on

DECLARE @.keyssql AS varchar(8000)

SET @.keyssql = 'SELECT * FROM View2 WHERE (Data = CONVERT(DATETIME,' +CHAR(39)+ '2004-01-05 00:00:00'+CHAR(39)+', 102))'

EXEC (@.keyssql)
/*--------------*/

This SP work OK.

I want to use a parameter inside '2004-01-05 00:00:00'

Atention EXEC (@.keyssql) is inside a SP|||Enigma, Sorry don't work ........|||didnt get that !!! did the sp not work ?|||This is the original SP
But an solution for the precedent example it would usefull for me.

Alter Procedure sp_CrossTab
@.table AS sysname,
@.onrows AS nvarchar(128),
@.onrowsalias AS sysname = NULL,
@.oncols AS nvarchar(128),
@.sumcol AS sysname = NULL,
@.avgcol AS sysname = NULL,
@.Grupa AS nvarchar(20),
@.datainceput AS datetime,
@.datasfirsit AS datetime

AS
set nocount on

DECLARE @.sql AS varchar(8000), @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

SET @.table = '['+ @.table + ']'
SET @.oncols = '['+ @.oncols + ']'
SET @.onrows = '['+ @.onrows + ']'
SET @.sumcol = '['+ @.sumcol + ']'
SET @.avgcol = '['+ @.avgcol + ']'

SET @.sql ='SELECT' + @.NEWLINE
+ 'DATEPART(ww,' + @.onrows+ ') AS Saptamina,' + ' '
+ 'DATEPART(mm,' + @.onrows+ ') AS Luna,' + ' '
+ 'DATEPART(yyyy,' + @.onrows+ ') AS Anul,' + ' '
+ @.onrows +

CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)

/* THIS PART DON'T WORK */

SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 120) +')'

/* THIS PART WORK OK*/
/*SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table*/

PRINT @.keyssql

EXEC (@.keyssql)

DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
...........................|||Use This >>>>
SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 104) +')'|||I try but the same error: "Error converting data type varchar to datetime"

Please help me,
Try an easy example:
One table with 3 columns (Day, Field1, Field2)
Create a SP and see if work.|||maybe i am not understanding the dateformat you are passing ...

check up convert in the holy book ("SQL Server Books Online") and insert the no corresponding to your input format|||I have a strong suspicion that the '15.01.2004' date is causing the problem. Whereever possible, you should feed 'yyyy-mm-dd' strings to the sql server. If you can't, then you need to declare your parameters as STRING instead of datetime since 15.01.2004 may not be a date if the date format on the Sql Server is set to 'mm-dd-yyyy' There is no 15th month (not on Earth, anyway ;)).