Varchar

Question

Post reply

Varchar

Mike Levan

SSChampion

Points: 14201
More actions
October 26, 2007 at 9:45 am

#62181

I have a column with Varchar(100) and it allows to enter numbers but it also allows to enter date like '1/3/1998' which ia wrong entry, I am not supposed to change the design but when i check to see if there is any data less than 1, i get an error
Syntax error converting the varchar value '1/3/1998' to a column of data type int.
how can i check with out getting this error.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

look in BOL under ISNUMERIC()

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

russell-154600 Ten Centuries Points: 1375 More actions · Answer 2

where isnumeric(fieldname) = 1

and convert(int, fieldname) < 1

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Mike Levan (10/26/2007)
I have a column with Varchar(100) and it allows to enter numbers but it also allows to enter date like '1/3/1998' which ia wrong entry, I am not supposed to change the design but when i check to see if there is any data less than 1, i get an error
Syntax error converting the varchar value '1/3/1998' to a column of data type int.
how can i check with out getting this error.

VARCHAR will allow users to enter anything they want. Are the values *only* int and mm/dd/yyyy format? Are the int values supposed to be representative of dates? If so you could convert all of them to dates. I still say a staging table will help you deal with your data.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

You seem to be running a few threads all on this same topic. I would suggest that you post a sample (10 or 20 rows) of the data in your varchar field and then explain what you want to locate in each of the rows. The we can work on this all at once.