August 5, 2008 at 8:59 am
I KEEP GETTING THIS ERROR WHEN I RUN THIS SCRIPT
Msg 241, Level 16, State 1, Line 26
Conversion failed when converting datetime from character string.
error is on my case statement..How do I get past it
DECLARE @sqlVARCHAR(8000)
,@ThreeMonthsAgoVARCHAR(10)
,@FourMonthsAgoVARCHAR(10)
,@FiveMonthsAgoVARCHAR(10)
,@FirstDay_FiveMonthsAgoDATETIME
,@LastDay_FiveMonthsAgoDATETIME
,@AbilDataCHAR(7)
SET @ThreeMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -3, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FourMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -4, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FiveMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -5, GETDATE()), 6), 6), ' ', '') + 'AMT'
--==== First Day Two Months Ago
SET @FirstDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()) - 5, 0))
--==== Last Day of Current Month
SET@LastDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1))
--==== Current AbilData Table that Joins
SET @AbilData = (SELECT
SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()
) - 6, 0 ),112),1,4)) + '_' +
SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()
) - 6, 0 ),112),5,2)
--==== Identify All Loans Whose first Instalment was due
SET @sql ='SELECT TOP 100
a.LoanRef
,a.FIRSTPERENDDATE
,a.Branch
,a.Balance
,a.EvenInstalment
,CASE WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0) THEN b.'
+ @ThreeMonthsAgo + ' *-1' + ' ELSE 0 END AS '
+ @ThreeMonthsAgo + CHAR(10) + CHAR(13) + SPACE(16)
+
',CASE WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0) THEN b.'
+ @FourMonthsAgo + ' *-1'+ ' ELSE 0 END AS ' + @FourMonthsAgo
+ CHAR(10) + CHAR(13) + SPACE(16)
+
',CASE WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0) THEN b.'
+ @FiveMonthsAgo + ' *-1'+ ' ELSE 0 END AS ' + @FiveMonthsAgo
+ CHAR(13) + CHAR(10) +
' ,e.Description
FROM
AData_' + @AbilData + ' a LEFT OUTER JOIN
BData c ON a.LoanRef = c.LoanRef LEFT OUTER JOIN
Deviate b ON a.LoanRef = b.LoanRef
LEFT OUTER JOIN
tbMBbranch e
ON a.branch = e.branchcode
WHERE CONVERT(VARCHAR(10),a.FIRSTPERENDDATE,120) BETWEEN ' +
REPLACE(CONVERT(VARCHAR(10),@FirstDay_FiveMonthsAgo,120),'-','') + ' AND ' +
REPLACE(CONVERT(VARCHAR(10),@LastDay_FiveMonthsAgo,120),'-','') + ' AND (a.Balance > 0) AND (c.Balance > 0)'+
'AND a.productcode NOT LIKE ''%CA%'''
--==== INSERT 'tb' + @StrCurrMonth
EXEC (@Sql)
August 5, 2008 at 9:11 am
Hi
You need to cast/convert your getdate values to VARCHAR's before trying to concatenate them to your main sql string.
Thats why you get the error.
Take note the you will need to have single quotes inside your dynamic sql so that the date in the main string is captured correctly e.g '2008-01-01'
Hope that helps
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 9:12 am
Could you provide more detail? What would really help is if you could provide the DDL for the tables (create table statements) (or at least the key columns for the query, including indexes), sample data (in the form of insert statements that can be cut, paste, and run to load the tables), and the expected results based on the sample data provided.
There may be a better way to accomplish what you are trying to do with dynamic sql.
😎
August 5, 2008 at 9:12 am
Try formatting a bit your code, or nobody will take the time to read it:
DECLARE @sql VARCHAR(8000),
@ThreeMonthsAgo VARCHAR(10),
@FourMonthsAgo VARCHAR(10),
@FiveMonthsAgo VARCHAR(10),
@FirstDay_FiveMonthsAgo DATETIME,
@LastDay_FiveMonthsAgo DATETIME,
@AbilData CHAR(7)
SET @ThreeMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -3, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FourMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -4, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FiveMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -5, GETDATE()), 6), 6), ' ', '') + 'AMT'
--==== First Day Two Months Ago
SET @FirstDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()) - 5, 0))
--==== Last Day of Current Month
SET @LastDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1))
--==== Current AbilData Table that Joins
SET @AbilData = (SELECT SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE() - 6, 0 ),112),1,4)) ù
+ '_' + SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE() - 6, 0 ),112),5,2)
--==== Identify All Loans Whose first Instalment was due
SET @sql ='
SELECT TOP 100 a.LoanRef ,
a.FIRSTPERENDDATE ,
a.Branch ,
a.Balance ,
a.EvenInstalment ,
CASE
WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @ThreeMonthsAgo + ' *-1' + '
ELSE 0
END AS ' + @ThreeMonthsAgo + CHAR(10) + CHAR(13) + SPACE(16) + ',
CASE
WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @FourMonthsAgo + ' *-1'+ '
ELSE 0
END AS ' + @FourMonthsAgo + CHAR(10) + CHAR(13) + SPACE(16) + ',
CASE
WHEN ' + GETDATE() + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + GETDATE() + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @FiveMonthsAgo + ' *-1'+ '
ELSE 0
END AS ' + @FiveMonthsAgo + CHAR(13) + CHAR(10) + ' ,
e.Description
FROM AData_' + @AbilData + ' a
LEFT OUTER JOIN BData c
ON a.LoanRef = c.LoanRef
LEFT OUTER JOIN Deviate b
ON a.LoanRef = b.LoanRef
LEFT OUTER JOIN tbMBbranch e
ON a.branch = e.branchcode
WHERE CONVERT(VARCHAR(10),a.FIRSTPERENDDATE,120)
BETWEEN ' + REPLACE(CONVERT(VARCHAR(10),@FirstDay_FiveMonthsAgo,120),'-','') + '
AND ' + REPLACE(CONVERT(VARCHAR(10),@LastDay_FiveMonthsAgo,120),'-','') + '
AND (a.Balance > 0)
AND (c.Balance > 0)'+ '
AND a.productcode NOT LIKE ''%CA%'''
--==== INSERT 'tb' + @StrCurrMonth
EXEC (@Sql)
-- Gianluca Sartori
August 5, 2008 at 9:16 am
oh and here is the code
DECLARE @sql VARCHAR(8000),
@ThreeMonthsAgo VARCHAR(10),
@FourMonthsAgo VARCHAR(10),
@FiveMonthsAgo VARCHAR(10),
@FirstDay_FiveMonthsAgo DATETIME,
@LastDay_FiveMonthsAgo DATETIME,
@AbilData CHAR(7)
SET @ThreeMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -3, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FourMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -4, GETDATE()), 6), 6), ' ', '') + 'AMT'
SET @FiveMonthsAgo = REPLACE(RIGHT(CONVERT(VARCHAR, DATEADD(MONTH, -5, GETDATE()), 6), 6), ' ', '') + 'AMT'
--==== First Day Two Months Ago
SET @FirstDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()) - 5, 0))
--==== Last Day of Current Month
SET @LastDay_FiveMonthsAgo = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1))
--==== Current AbilData Table that Joins
SET @AbilData = (SELECT SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()) - 6, 0 ),112),1,4))
+ '_' + SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH, 0 , GETDATE()) - 6, 0 ),112),5,2)
--==== Identify All Loans Whose first Instalment was due
SET @sql ='
SELECT TOP 100 a.LoanRef ,
a.FIRSTPERENDDATE ,
a.Branch ,
a.Balance ,
a.EvenInstalment ,
CASE
WHEN ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @ThreeMonthsAgo + ' *-1' + '
ELSE 0
END AS ' + @ThreeMonthsAgo + CHAR(10) + CHAR(13) + SPACE(16) + ',
CASE
WHEN ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @FourMonthsAgo + ' *-1'+ '
ELSE 0
END AS ' + @FourMonthsAgo + CHAR(10) + CHAR(13) + SPACE(16) + ',
CASE
WHEN ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND ' + CONVERT(VARCHAR(10),GETDATE(),120) + ' < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) +1, 0)
THEN b.' + @FiveMonthsAgo + ' *-1'+ '
ELSE 0
END AS ' + @FiveMonthsAgo + CHAR(13) + CHAR(10) + ' ,
e.Description
FROM AData_' + @AbilData + ' a
LEFT OUTER JOIN BData c
ON a.LoanRef = c.LoanRef
LEFT OUTER JOIN Deviate b
ON a.LoanRef = b.LoanRef
LEFT OUTER JOIN tbMBbranch e
ON a.branch = e.branchcode
WHERE CONVERT(VARCHAR(10),a.FIRSTPERENDDATE,120)
BETWEEN ' + REPLACE(CONVERT(VARCHAR(10),@FirstDay_FiveMonthsAgo,120),'-','') + '
AND ' + REPLACE(CONVERT(VARCHAR(10),@LastDay_FiveMonthsAgo,120),'-','') + '
AND (a.Balance > 0)
AND (c.Balance > 0)'+ '
AND a.productcode NOT LIKE ''%CA%'''
--==== INSERT 'tb' + @StrCurrMonth
EXEC (@Sql)
That should be what you looking for.
The error is not in your case statement, as it's more of a syntax error with string building.
🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 6, 2008 at 3:10 am
Thanks. I restructured the string and put my Getdate() is single quotes and ran it and it worked ...Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply