Query - 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)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-26 : 11:03:27

[code]

Below is my query when executed i got the error

Saying :Syntax error converting the varchar value '85sd' to a column of data type int.

Select 'STUDENT GROUP: 15-50' as STUDENTAGE,
Sum(Case When CONVERT(int,JYear) - CONVERT(int, BYear) Between 15 And 50
Then 1
Else 0
End) as count

FROM dbo.studenttable
WHERE BYear not like '%[^0-9]%' AND Jyear not like '%[^0-9]%'

Sample date in the table:

BYear Jyear
---- ----
1957 85sd
1956 2008
1965 85sd
1961 85sd
1971 2008
1970 85sd
1958 85sd
1976 2008
1976 2008
1976 2008
19l7 2008

I want to exclude the year from BYear, Jyear which are invalid value..

Please help..

[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-27 : 00:16:20

In the table, u hav varchar value (85sd), that's why query returns error. check it once

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-27 : 07:36:37

quote:

Originally posted by sqlfresher2k7



Below is my query when executed i got the error

Saying :Syntax error converting the varchar value '85sd' to a column of data type int.

Select 'STUDENT GROUP: 15-50' as STUDENTAGE,               
       Sum(Case When CONVERT(int,JYear) - CONVERT(int, BYear) Between 15 And 50              
                Then 1     
                Else 0              
                End) as count              
              
  FROM dbo.studenttable
WHERE BYear not like '%[^0-9]%' AND Jyear not like '%[^0-9]%'

Sample date in the table:

BYear   Jyear
----    ----
1957	85sd
1956	2008
1965	85sd
1961	85sd
1971	2008
1970	85sd
1958	85sd
1976	2008
1976	2008
1976	2008
19l7    2008

I want to exclude the year from BYear,  Jyear which are invalid value..

Please help..

The query should work!
Maybe the original query looks different?

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-27 : 10:48:17

I modified the query with below changes..

Select 'STUDENT GROUP: 15-50' as STUDENTAGE,
Sum(Case When CONVERT(int,JYear) - CONVERT(int, BYear) Between 15 And 50
Then 1
Else 0
End) as count

FROM dbo.studenttable
WHERE isdate(BYear) =1 AND ISDate(Jyear) =1

It works..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources