Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion failed when converting datetime from character string Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 6:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 12:15 PM
Points: 5, Visits: 43
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
Post #981994
Posted Tuesday, September 7, 2010 8:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,364, Visits: 8,951
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #982009
Posted Wednesday, September 8, 2010 12:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 411, Visits: 1,406
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #982062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse