October 9, 2015 at 9:29 am
DECLARE @SQL NVARCHAR(400)
DECLARE @WgnWCUser VARCHAR(30)='DBO'
DECLARE @TIMESTAMP VARCHAR(30)='UpdateTimestamp'
DECLARE @datFrom DATETIME='2015-09-16'
DECLARE @datTo DATETIME='2015-09-30'
SET @SQL='SELECT EVENTUID,EVENTTIMESTAMP,EVENTMAJORTYPE,EVENTMINORTYPE INTO #TEMP
FROM ' + @WgnWCUser + '.EMPLOYEE A WITH(NOLOCK)
WHERE A.'+@TIMESTAMP+'>=CAST('+@datFrom+' AS DATE)AND A.'+@TIMESTAMP+'<CAST('+@datTo+' AS DATE)'
PRINT @SQL
--gETTING BELOW eRROR
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.
October 9, 2015 at 9:33 am
Something like this, perhaps?
SET @SQL='SELECT EVENTUID,EVENTTIMESTAMP,EVENTMAJORTYPE,EVENTMINORTYPE INTO #TEMP
FROM ' + @WgnWCUser + '.EMPLOYEE A WITH(NOLOCK)
WHERE A.'+@TIMESTAMP+' >= ''' +convert(char(8), @datFrom, 112) + ''' AND A.'+@TIMESTAMP+' < ''' + convert(char(8), @datFrom, 112)+ ''''
Gerald Britton, Pluralsight courses
October 9, 2015 at 9:38 am
It worked. Thanks .. I appreciate your help.
SET @SQL='SELECT EVENTUID,EVENTTIMESTAMP,EVENTMAJORTYPE,EVENTMINORTYPE INTO #TEMP
FROM ' + @WgnWCUser + '.EMPLOYEE A WITH(NOLOCK)
WHERE A.'+@TIMESTAMP+' >= ''' +convert(varchar(12), @datFrom, 112) + ''' AND A.'+@TIMESTAMP+' < ''' + convert(varchar(12), @datFrom, 112)+ ''''
PRINT @SQL
October 9, 2015 at 9:51 am
Slightly more pedantic approach preventing an implicit conversion
😎
DECLARE @SQL NVARCHAR(400)
DECLARE @WgnWCUser VARCHAR(30)='DBO'
DECLARE @TIMESTAMP VARCHAR(30)='UpdateTimestamp'
DECLARE @datFrom DATETIME='20150916'
DECLARE @datTo DATETIME='20150930'
SET @SQL='SELECT EVENTUID,EVENTTIMESTAMP,EVENTMAJORTYPE,EVENTMINORTYPE INTO #TEMP
FROM ' + @WgnWCUser + '.EMPLOYEE A WITH(NOLOCK)
WHERE A.'+@TIMESTAMP+'>=CONVERT(DATE,'''+CONVERT(VARCHAR(25),@datFrom,112)+''',112)AND A.'+@TIMESTAMP+'< CONVERT(DATE,'''+CONVERT(VARCHAR(25),@datTo,112)+''',112)'
PRINT @SQL;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply