...

View Full Version : Convert an char field into Integer on SQL Statement



paulafernandes
02-04-2003, 12:52 PM
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
02-04-2003, 01:22 PM
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
02-04-2003, 01:25 PM
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
02-04-2003, 01:40 PM
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
02-04-2003, 02:46 PM
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
02-04-2003, 03:54 PM
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
02-04-2003, 04:36 PM
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...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum