January 13, 2017 at 1:44 am
Hi Experts,
While running the query on different months work fine but not running on 31-December-2016 ,please help out.
SELECT SUM(vol)
FROM TABLE
WHERE C_DATE >= CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)
AND C_DATE < CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')+1) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
January 13, 2017 at 2:06 am
smer - Friday, January 13, 2017 1:44 AMHi Experts,While running the query on different months work fine but not running on 31-December-2016 ,please help out.
SELECT SUM(vol)
FROM TABLE
WHERE C_DATE >= CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)
AND C_DATE < CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')+1) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I see several potential problems here.
When comparing date (or datetime etc.) they should be compared as the date (or datetime) datatypes.
If C_Date is a string type first convert this to a date and then compare.
-- Example
select convert(datetime,'20060612 18:18:38',126) >= '20161231'
-- Use C_Date instead of the string. Google on 'SQLServer CAST and CONVERT') form more info about formats.
If C_Date is a type of the datetime 'system' then compare with a datetype and not with a string.
--
Example
C_DATE >= '20161231'
AND
C_DATE < DATEADD ( MONTH ,1, '20161231')
See CAST and CONVERT for more information about date / time formats.
Year with four numeric characters is preferred. (Month in two, Day in two)
Year Month Day order is preferred.
Ben
January 13, 2017 at 2:06 am
as an advise whenever doing this type of conversions always select the individual parts on their own to see what the resulting value is.
SELECT SUM(vol)
FROM TABLE
WHERE C_DATE >= CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)
AND C_DATE < CAST(CONVERT(VARCHAR, YEAR('31-DEC-2016')) + '-' + CONVERT(VARCHAR, MONTH('31-DEC-2016')+1) + '-' + CONVERT(VARCHAR, 1)AS DATETIME)
January 13, 2017 at 2:29 am
You should always use a universally recognised date format so that your query works in whatever environment you happen to run it. And don't use string manipulations - they're slower and more cumbersome than date arithmetic. Try the below. It works by calculating the number of months between an arbitrary date in the past and the given date, then adding that number back on to the arbitrary date, thus giving the first of the month of the given date.DECLARE @MyDate datetime2(7) = '20161231';
SELECT SUM(vol)
FROM
WHERE C_DATE >= DATEADD(Month,DATEDIFF(Month,'19000101',@MyDate),'19000101')
AND c_DATE < DATEADD(Month,DATEDIFF(Month,'19000101',@MyDate)+1,'19000101');
John
January 14, 2017 at 6:56 am
John Mitchell-245523 - Friday, January 13, 2017 2:29 AMYou should always use a universally recognised date format so that your query works in whatever environment you happen to run it. And don't use string manipulations - they're slower and more cumbersome than date arithmetic. Try the below. It works by calculating the number of months between an arbitrary date in the past and the given date, then adding that number back on to the arbitrary date, thus giving the first of the month of the given date.DECLARE @MyDate datetime2(7) = '20161231';
SELECT SUM(vol)
FROMWHERE C_DATE >= DATEADD(Month,DATEDIFF(Month,'19000101',@MyDate),'19000101')
AND c_DATE < DATEADD(Month,DATEDIFF(Month,'19000101',@MyDate)+1,'19000101');
John
Thank you all for your suggestions
John Mitchell- Many thanks for your suggestion i keep in my mind ...
The query it works like a charm 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply