Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Nov 2002
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Convert an char field into Integer on SQL Statement

    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

  • #2
    New Coder
    Join Date
    Feb 2003
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    New Coder
    Join Date
    Nov 2002
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry... I should have told that in my post...

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

    Paula

  • #4
    New Coder
    Join Date
    Feb 2003
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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/de...conversion.asp

    Hope it helps. Good luck

  • #5
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #7
    New Coder
    Join Date
    Nov 2002
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •