View Full Version : Convert an char field into Integer on SQL Statement
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?
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.
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... :-)
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:
Hope it helps. Good luck
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.
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.
02-04-2003, 04:36 PM
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!!!
P.S.: I'll be back...
Powered by vBulletin® Version 4.2.2 Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.