Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    13

    Unanswered: Converting to date format

    I am trying to run a select statement with a field in the where clause that I want to check the Month value of, but it is in a decimal field. The format that it currently is in is (YYYYMMDD). Any ideas on how to change the format of the field in the where clause? I need to be able to do it as follows:

    select * from table1 where month(field1) = 10

    value in field1 = 20041015

    Thanks in advance for any help,
    Jeremy

  2. #2
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    why dont you select all the records based on the where condition
    then convert the variable to date. Check it, if it is what you want

    continue
    else
    continue foreach
    --get the next record

    or something like that

    hope this helps

    Cheers

    Artem

  3. #3
    Join Date
    Sep 2004
    Posts
    13
    Artem,
    Thanks for the quick reply. I will give that a try. I am new to the Informix world (as is obvious by some of my posts). I have been spoiled by some of the functions in SQL server, and I have also inherited a less than desireable data model. Guess we have to play with the hand that is dealt us.

    Thanks,
    Jeremy

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Use substr() to retrive information as below:

    select * from tabx where substr(lpad(num_dt_field,8,'0'),5,2)='10' ;

  5. #5
    Join Date
    Sep 2004
    Posts
    13
    Thanks for the reply. I will give that a try.

    Jeremy

Posting Permissions

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