Conversion failed when converting datetime from character string

Question

Post reply

Conversion failed when converting datetime from character string

chloe82402

SSC Veteran

Points: 297
More actions
September 7, 2010 at 6:18 pm

#239958

Hello there. I'm having an issue that I can't seem to resolve with my date.
I get this error message when I run the below code. Thanks
Msg 241, Level 16, State 1, Line 412
Conversion failed when converting datetime from character string.
DECLARE @MONTH VARCHAR (20)
SET @MONTH = '20100801'
SELECT BASE.MARKET_CD,
BASE.PRODUCT_CD,
BASE.COS_CD,
BASE.SERVICE_YR_MO,
BASE.PAID_YR_MO,
RPT_COL = 'PAID ',
CASE WHEN @MEASURE = 1 THEN SUM(BASE.PAYMENT_AMT)
WHEN SUM(M.MBR_CNT) = 0 THEN 0
ELSE SUM(BASE.PAYMENT_AMT)/SUM(M.MBR_CNT) END AS PAYMENT_AMT
INTO #REPORT_DATA
FROM #DATA BASE
LEFT JOIN #MEMBERSHIP M
ON BASE.MARKET_CD = M.MARKET_CD COLLATE SQL_Latin1_General_CP437_BIN
AND BASE.PRODUCT_CD = M.PRODUCT_CD COLLATE SQL_Latin1_General_CP437_BIN
AND BASE.PAID_YR_MO = M.SERVICE_YR_MO
WHERE
BASE.SERVICE_YR_MO BETWEEN CONVERT(CHAR(6),DATEADD(M,-24,CONVERT(datetime,@MONTH + '01',112)),112) AND @MONTH
AND
BASE.PAID_YR_MO BETWEEN CONVERT(CHAR(6),DATEADD(M,-15,CONVERT(datetime,@MONTH + '01',112)),112) AND @MONTH
GROUP BY BASE.MARKET_CD,
BASE.PRODUCT_CD,
BASE.COS_CD,
BASE.SERVICE_YR_MO,
BASE.PAID_YR_MO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You're setting @Month = '20100801'

Then, in you're doing:

BASE.SERVICE_YR_MO BETWEEN CONVERT(CHAR(6),DATEADD(M,-24,CONVERT(datetime,@MONTH + '01',112)),112) AND @MONTH

AND

BASE.PAID_YR_MO BETWEEN CONVERT(CHAR(6),DATEADD(M,-15,CONVERT(datetime,@MONTH + '01',112)),112) AND @MONTH

Adding the string '01' to '20100801' gives you '2010080101', which is not a valid date.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

Well spotted, WayneS.

Apart from that error in the string manipulation, it's better (for performance and to avoid internationalization issues) not to use string manipulation at all on your datetime values. The functions datediff() and dateadd() were designed to manipulate those values.

To retrieve the first day of the current month you can use:

select dateadd(month, datediff(month, 0, getdate()), 0)

Next month's first day is:

select dateadd(month, datediff(month, 0, getdate()), 1)

Similar, to strip off the time part from any datetime value (= find the beginning of the day, 00:00:00.000 in the time component), use:

select dateadd(day, datediff(day, 0, getdate()), 0)

Plus, you're using between on datetime values. Since between is inclusive, you should better use "where @dtvalue >= @dtFromValue and @dtvalue < @dtToValue" instead of "where @dtValue between @dtValueFrom and @dtValueTo" to avoid incorrectly getting rows for the first day of the next month included in your results.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?