Convert an char field into Integer on SQL Statement

[paulafernandes]

Hello! Here I am again...

I need you help in this:
I have on a table of the database a field of type char, were I store a date on the format 01-01-1900.
I need to do a comparision with a year choosed by the user.
For example, the user chooses 1990, and I do year = "01-01-" &'1990'

I want to use a CONVERT() on my SQL statement so I can convert this field (char) to a integer, and then do the comparision. But I can find the right way to use this function. Something like this:
Ex.: strsql = "select antique from tab_func where CONVERT(antique) >= " &year
Obviosly, this doesn't work...

Can anobody help me or tell me were I can find information about this?

Thank's!
Paula

[GoHF]

I've been looking over at MySQL's manual and it seems CONVERT(char, date) only happens on versions 4.0.6+. So could you please tell us which database you are using and it's version? =)

http://www.mysql.com/doc/en/Cast_Functions.html <- take a look, it might help.

[paulafernandes]

Sorry... I should have told that in my post...

I'm using SQL Server 7.0.
I hope that's a good thing... :-)

Paula

[GoHF]

Ok, the most I can help you, as I personally am not an SQL expert and I dont have SQL Server, is give you this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcexplicit_data_type_conversion.asp

Hope it helps. Good luck

[arnyinc]

The "date" datatype would have taken care of everything for you. If you can convert your field to a date datatype, I would do that. Otherwise, you can convert them to dates with cdate() and compare them that way.

[whammy]

What arnyinc said. If you can convert it to a "Date" datatype, then you can probably use the DATEDIFF() function...

"SELECT * FROM tablename WHERE DATEDIFF(Day,CONVERT(your date field here etc),'" & someuserdate & "') < 10"

Not totally sure on the syntax and I don't have SQL Server handy since I'm home sick as a dog, but if you can get the convert function to work that idea should work. Or maybe CAST() instead...

I find it much easier to store a true datetime field in the database, usually as a default value.

In your table's design view, you will see an option below for "default value". You can use (getdate()) as the default value and that will put in a datetime whenever a record is inserted.

[paulafernandes]

Hi!

I wasn't using a datetime type because my "boss" (actually, an older co-worker, that I'm working with) "sugested" it to me. He doesn't like the datetime, says it gives to much problems.

But, I changed it now, and solved the problem really fast...

Anyway, thank's for all the help!!!

Paula

P.S.: I'll be back...