Help with Error Message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Error Message

Author

Topic

edallas
Starting Member

1 Post

Posted - 2014-05-01 : 11:02:17

Hello All,
I have been using the same query without changing anything but the actual date and until today have never encountered an error.

Error message is:
Server: Msg 241, Level 16, State 1, Line 7
Syntax error converting datetime from character string.

Query is:
declare @Begin_weekend datetime
declare @End_weekend datetime

set @Begin_weekend = '04/26/14'
set @End_weekend = '04/26/14'

select DayofWeek,
sum(case facility when 'brattleboro, vt' THEN stops else 0 END) as 'Brattleboro',
sum(case facility when 'Hatfield North, MA' THEN stops else 0 END) as 'Hatfield',
sum(case facility when 'Windsor Locks, CT' THEN stops else 0 END) as 'Windsor Locks',
sum(case facility when 'Westfield, MA' THEN stops else 0 END) as 'Westfield',
sum(case facility when 'Newburgh, NY' THEN stops
when 'Montgomery, NY' THEN stops
else 0 END) as 'Newburgh',

sum(case facility when 'Aberdeen, MD' THEN stops else 0 END) as 'Aberdeen',
sum(case facility when 'York, PA' THEN stops else 0 END) as 'York, PA (FDC)',
sum(case facility when 'ES3 York, PA' THEN stops else 0 END) as 'D2S',
sum(case facility when 'Chester, NY' THEN stops else 0 END) as 'Chester, NY (PDC)',
sum(case facility when 'Chester II, NY' THEN stops else 0 END) as 'Chester II, NY (PDC)',
sum(case facility when 'Suffield, CT' THEN stops else 0 END) as 'Suffield',
sum(case facility when 'Northeast, MD' THEN stops else 0 END) as 'Northeast',
sum(case facility when 'Bethlehem, PA' THEN stops else 0 END) as 'Bethlehem I, PA (GDC)',
sum(case facility when 'Bethlehem2, PA' THEN stops else 0 END) as 'Bethlehem II, PA (GDC)',
sum(case facility when 'Edison, NJ' THEN stops else 0 END) as 'Edison, NJ (GMDC)',
sum(case facility when 'Du Bois, PA PERISH' THEN stops else 0 END) as 'Dubois, PA (FDC/PDC)',
sum(case facility when 'Du Bois, PA GROCERY' THEN stops else 0 END) as 'Dubois, PA (GDC)',
sum(case facility when 'Stockton, CA' THEN stops else 0 END) as 'Stockton',
sum(case facility when 'Fresno, CA' THEN stops else 0 END) as 'Fresno',
sum(case facility when 'Sacramento GMD, CA' THEN stops else 0 END) as 'Sacramento',

sum(case facility when 'Baldwin-Grocery' THEN stops
when 'Baldwin-GMD' THEN stops
else 0 END) as 'Baldwin Grocery',
sum(case facility when 'Baldwin-Frozen' THEN stops
when 'Baldwin-Perish' THEN stops
when 'Baldwin-Combo' THEN stops
else 0 END) as 'Baldwin Per/Fro',

sum(case facility when 'Hammond-Grocery' THEN stops else 0 END) as 'Hammond Grocery',
sum(case facility when 'Hammond-Frozen' THEN stops
when 'Hammond-Perish' THEN stops
when 'Hammond-Combo' THEN stops
else 0 END) as 'Hammond Per/Fro',

sum(case facility when 'Miami-Grocery' THEN stops
when 'Miami-GMD' THEN stops
else 0 END) as 'Miami Grocery',
sum(case facility when 'Miami-Frozen' THEN stops
when 'Miami-Perish' THEN stops
when 'Miami-Combo' THEN stops
when 'Miami-Milk' THEN stops
else 0 END) as 'Miami Per/Fro',
sum(case facility when 'Orlando-Grocery' THEN stops
when 'Orlando-GMD' THEN stops
else 0 END) as 'Orlando Grocery',
sum(case facility when 'Orlando-Frozen' THEN stops
when 'Orlando-Perish' THEN stops
else 0 END) as 'Orlando Per/Fro',

sum(case facility when 'Montgomery-Grocery' THEN stops
when 'Montgomery-GMD' THEN stops
else 0 END) as 'Montgomery Grocery',
sum(case facility when 'Montgomery-Combo' THEN stops
when 'Montgomery-Perish' THEN stops
when 'Montgomery-Frozen' THEN stops
else 0 END) as 'Montgomery Per/Fro'
from (
Select facility, [route #], DayofWeek, stops

from (
Select facility,[route #]+[route group] as [route #], DatePArt(Weekday, Dispatch) AS DayofWeek, stops
from tbl_masterdata
where Cast(Left(Right(week_ending, 6), 2) + '/' + Right(Left(week_ending, 6), 2) + '/' + Right(week_ending, 2) As Datetime) >= @Begin_weekend and Cast(Left(Right(week_ending, 6), 2) + '/' + Right(Left(week_ending, 6), 2) + '/' + Right(week_ending, 2) As Datetime) <= @End_weekend
AND [Route #]+[Route Group] IN

(
select [route #]+[route group] as [route #]
from tbl_masterdata
where
Cast(Left(Right(week_ending, 6), 2) + '/' + Right(Left(week_ending, 6), 2) + '/' + Right(week_ending, 2) As Datetime) >= @Begin_weekend and Cast(Left(Right(week_ending, 6), 2) + '/' + Right(Left(week_ending, 6), 2) + '/' + Right(week_ending, 2) As Datetime) <= @End_weekend
AND ledgend IN ('del','shu')
)
)b
group by facility, [route #], dayofweek, stops
)c
group by dayofweek
order by dayofweek

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-01 : 13:10:51

You probably have problems with the data in week_ending:


SELECT LEFT(RIGHT(week_ending, 6), 2) + '/' + RIGHT(LEFT(week_ending, 6), 2) + '/' + RIGHT(week_ending, 2), *
FROM tbl_masterdata
WHERE ISDATE(LEFT(RIGHT(week_ending, 6), 2) + '/' + RIGHT(LEFT(week_ending, 6), 2) + '/' + RIGHT(week_ending, 2)) = 0;

Also, it is best to enter date constants in ISO format.
eg:


SET @End_weekend = '20140426';

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-02 : 01:32:36

Another benefit of using date in ISO format is integration with other systems. http://www.sqlserver-dba.com/2012/10/sql-server-datetime-date-range-as400-and-openquery.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources