InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:38:50 GMT20http://www.sqlservercentral.com/Forums/Topic981994-338-1.aspxWell 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:[code="sql"]select dateadd(month, datediff(month, 0, getdate()), 0)[/code]Next month's first day is:[code="sql"]select dateadd(month, datediff(month, 0, getdate()), 1)[/code]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:[code="sql"]select dateadd(day, datediff(day, 0, getdate()), 0)[/code]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.Wed, 08 Sep 2010 00:09:02 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic981994-338-1.aspxYou'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 @MONTHANDBASE.PAID_YR_MO BETWEEN CONVERT(CHAR(6),DATEADD(M,-15,CONVERT(datetime,@MONTH + '01',112)),112) AND @MONTHAdding the string '01' to '20100801' gives you '2010080101', which is not a valid date.Tue, 07 Sep 2010 20:47:11 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic981994-338-1.aspxHello 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. ThanksMsg 241, Level 16, State 1, Line 412Conversion 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_AMTINTO #REPORT_DATAFROM #DATA BASELEFT 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_MOWHERE 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 @MONTHGROUP BY BASE.MARKET_CD, BASE.PRODUCT_CD, BASE.COS_CD, BASE.SERVICE_YR_MO, BASE.PAID_YR_MOTue, 07 Sep 2010 18:18:37 GMTchloe82402