SQL Error when trying to Convert/Cast data types

[leeland]

Now I am trying to do what I would think is a simple data type conversion. I have never really done one in SQL so go gentle on me if I did it wrong.

The code is pretty straight forward but I will attach it anyways


I am trying to figure out why it doesn't work...I get the error message stated above in the topic summary.

I am sure this is something pretty easy but I don't really ahve a clue and I have looked for an hour on google with no luck


thanks for any help

Leeland

 

[MrChad]

Run this query:

SELECT * FROM AdmVisits WHERE Age = '2Y 03M'

Your query hit a value it could not cast into an integer.

 

[leeland]

Originally posted by: MrChad
Run this query:

SELECT * FROM AdmVisits WHERE Age = '2Y 03M'

Your query hit a value it could not cast into an integer.

That makes total sense, I never did actually look for that field in the table...now would that be because of the field size I has specified or would it be because of the charaters within that field ?


Thanks I will investiagte that query right now and let you know what I find

Leeland


EDIT:

Just ran that query and it pulled back like 3,000 records with that as the age...guess I will have to forget about the casting ....


Thanks again for pointing out the obvious to me as far as figuring out it was a field that couldn't be casted...


Leeland

 

[MrChad]

Originally posted by: leeland

Originally posted by: MrChad
Run this query:

SELECT * FROM AdmVisits WHERE Age = '2Y 03M'

Your query hit a value it could not cast into an integer.

That makes total sense, I never did actually look for that field in the table...now would that be because of the field size I has specified or would it be because of the charaters within that field ?


Thanks I will investiagte that query right now and let you know what I find

Leeland

It would be because of the characters in that field. The all need to be integers in order to do a straight CAST() (e.g. '3', '5', '16').

 

[notfred]

Age is a stupid field to have in a database. Use "birthday", or your data is wrong all the time, because your database doesn't automatically update every time someone gets older.

 

[leeland]

Originally posted by: MrChad

It would be because of the characters in that field. The all need to be integers in order to do a straight CAST() (e.g. '3', '5', '16').

So I totally understand you....

it would have worked if I would ahve had just numbers in the fields like '456' or '001' because even though they are of data type varchar...they can be CASTED or CONVERTED to an int

The second you toss in a letter it screws the entire thing up (i.e. '123a' since that 'a' character can't be converted to a number value it bombs out the query.

does this soudn feasible ?


Thanks Mr. Chad for the quick response

Leeland

 

[MrChad]

Originally posted by: leeland

Originally posted by: MrChad

It would be because of the characters in that field. The all need to be integers in order to do a straight CAST() (e.g. '3', '5', '16').

So I totally understand you....

it would have worked if I would ahve had just numbers in the fields like '456' or '001' because even though they are of data type varchar...they can be CASTED or CONVERTED to an int

The second you toss in a letter it screws the entire thing up (i.e. '123a' since that 'a' character can't be converted to a number value it bombs out the query.

does this soudn feasible ?


Thanks Mr. Chad for the quick response

Leeland

Correct. If they are stored in a standardized format, you can play around with substring functions and convert to an INT that way.

 

[leeland]

Originally posted by: notfred
Age is a stupid field to have in a database. Use "birthday", or your data is wrong all the time, because your database doesn't automatically update every time someone gets older.

Great point notFred,

I have changed to the BirthDateTime Field.

Here is what I am trying to accomplish. I only need to pull records from people who are older than 65.

So I have tried this in my where clause


YEAR(AdmVisits.BirthDayTime) >= YEAR(GetDate()) - 65
This one just checks the years to see if they quailify

or

AdmVisits.BirthDayTime >= (GetDate()) - 23725
it is crude I know, but this on checks on the age of 65 from the current date, then only people who are over 65 are selected


I am getting another Damn error when I run this though

it is saying that

Invalid column name 'BirthDayTime'

EDIT: I had a misspelling in here it is supposed to be BirthDateTime. I have been looking too long at this stuff today

if I take out the statements in the WHERE clause it runs just fine...so I must be hosing something up in my statements

Thanks again guys for the help

Leeland