convert error in 2000, worked fine in 7.0

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

Transact-SQL (2000)

convert error in 2000, worked fine in 7.0

Author

Topic

smithje
Starting Member

32 Posts

Posted - 2004-01-29 : 16:39:42

What did they change in 2000 that is csusing the error: [CAST or CONVERT: invalid attributes specified for type 'int'] or [CAST or CONVERT: invalid attributes specified for type 'smalldatetime']? The query works in SQL 7.0 just fine.
Excerpt from actual code:

convert(varchar(10),
right(((datediff(mi,(convert(smalldatetime(18),Left(TI_LBLMIL,Len(TI_LBLMIL)-2) + ':' + right(TI_LBLMIL,2))),BUTTN_TIME )/60) * 100) +
(datediff(mi,(convert(smalldatetime(18),Left(TI_LBLMIL,Len(TI_LBLMIL)-2) + ':' + right(TI_LBLMIL,2))),BUTTN_TIME ) +
ABS(convert(int(6),datediff(mi,(convert(smalldatetime(18),Left(TI_LBLMIL,Len(TI_LBLMIL)-2) + ':' + right(TI_LBLMIL,2))),BUTTN_TIME ))/60) * 60),1))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:43:53

It compiles fine on my Query Analyzer 2000. Could you strip it down a bit so that we can see what is going on?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-29 : 16:51:23

I'm guessing that a character type is an invalid string representing INT or smalldatetime and can't be converted.

smithje
Starting Member

32 Posts

Posted - 2004-01-29 : 16:52:49

Here is another query from the same group that results in the same error on SQL 2000 but works fine 7.0.

SELECT convert(int(10),datediff(mi, R.SCHD_TIME, BUTTN_TIME )) AS THIS

From
(select * from tbl_cmsroute where creat_date = '8/7/2003') R
right join (select * from tbl_cmsummry where scan_date = '8/7/2003') S ON R.LocID = S.LocID and R.DOW_RUN = S.DOW_RUN
left join (select * from tbl_cmstatus where scan_date = '8/7/2003') T
ON R.LocID = T.LocID left join Portland_ClusterDB.dbo.tbl_Cmdata D ON
R.LocID = D.DA_LocID left join Portland_ClusterDB.dbo.tbl_Cmtimes M ON R.LOCID = M.ti_LocID and
(datepart(hour,R.schd_time) * 100 + datepart(minute,R.schd_time)) = M.TI_MILTIME and R.Dow_Run = M.Ti_Day
join Portland_ClusterDB.dbo.tbl_CmPoInfo F ON M.TI_ORIABRV = F.PO_ABRV join Portland_ClusterDB.dbo.tbl_CMofficeID E ON
F.PO_ABRV = E.PO_ABRV join Portland_ClusterDB.dbo.tbl_Offices O ON E.OfficeID = O.OfficeID
WHERE convert(int(10),datediff(mi, R.SCHD_TIME, BUTTN_TIME )) < 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:53:22

Yeah we would need to see some sample data to figure out where the problem is.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:55:13

Oh, nevermind, just remove (10) for INT. So your second query should say:

WHERE convert(int,datediff(mi, R.SCHD_TIME, BUTTN_TIME )) < 0

Tara

smithje
Starting Member

32 Posts

Posted - 2004-01-29 : 17:02:25

OK I deleted the (10) in both lines and the error goes away. This line or a similar one exists in multiple stored procedures on our SQL 7 server and all are broken as we convert to 2000. Can anyone explain why this happening now?

And THANKS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 17:11:47

SQL Server 7.0 doesn't allow you to specify INT(10) when you create a table. INT has 4 storage bytes. SQL Server 7.0 must have been forgiving in your query.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources