Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    12

    Unanswered: Informix syntax error

    Hi,

    This statement gives syntax error, but there isn't any detailed information about the error. Which part can be wrong ?
    This statement works correct in SQL Server.

    DECLARE @mydate DATETIME
    SELECT @mydate = getdate()
    SELECT cast(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,-1,@mydate))-1),DATEADD(mm,-1,@mydate)),101) as datetime) as BasTar,
    cast(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) as Datetime) as BitTar

  2. #2
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    I don't speak SQL server.
    What do you want to do?

  3. #3
    Join Date
    Dec 2008
    Posts
    12
    I want to take the first and the last day's date of the previous month.

  4. #4
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    I did my best

    you can use:
    Code:
    SELECT (current - day(current - 1 units month) units day)::DATETIME year to day BasTar,
    (current - day(TODAY) units day)::DATETIME year to day BitTar
    from systables
    where tabid=1;
    or
    Code:
    SELECT (current - day(current - 1 units month) units day)::DATE BasTar,
    (current - day(TODAY) units day)::DATE BitTar
    from systables
    where tabid=1;
    Note that informix in sql does not have variables (@mydate in your case) and there must be "FROM" part. Also, Informix have DATETIME and DATE types. (for your query I thing second one is more suitable)
    HTH

    I did not see your last post, so these queries are rewritten your query and they do not work as you expect:
    I want to take the first and the last day's date of the previous month.
    Last edited by ibm.ids; 12-17-08 at 09:35.

  5. #5
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    Try this:

    Code:
    select MDY(month((current - 1 units month)),1,year((current - 1 units month))) BasTar,
           MDY(month(TODAY),1,year((current - 1 units month))) - 1 BitTar
    from systables
    where tabid=1;
    HTH

  6. #6
    Join Date
    Dec 2008
    Posts
    12
    Thank you very much.

    Now the result is:

    BasTar(StartingDate) : 01.11.2008 00:00:00
    BitTar(EndDate): 30.11.2008 00:00:00


    How can i arrange the end date to 30.11.2008 23:59:59

    And is there a document that describe these informix info you recommend.
    Because I'm new in informix and can't understand the statement above

  7. #7
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    In Iformix there are types DATE and DATETIME. DATE type can store just day, month and year. DATETIME type can be defined like
    Code:
    DATETIME precision_from TO precision_to
    precision_from and precision_to can be from YEAR to FRACTION(5) e.g.
    Code:
    DATETIME YEAR TO SECOND
    DATETIME MINUTE TO SECOND
    etc.
    In your case, I think DATE type is right.
    So your query can be something like this:
    Code:
    select (MDY(month((current - 1 units month)),1,year((current - 1 units month))))::DATE BasTar,
           (MDY(month(TODAY),1,year((current - 1 units month))) - 1)::DATE BitTar
    from systables
    where tabid=1;
    If you need higher granulation, you can use DATETIME YEAR TO SECOND type like this:
    Code:
    select (MDY(month((current - 1 units month)),1,year((current - 1 units month))))::DATETIME year to second BasTar,
           (MDY(month(TODAY),1,year((current - 1 units month))))::DATETIME year to second - 1 units second BitTar
    from systables
    where tabid=1;
    Some comments about above code:
    - current is current moment in time (something like getdate() function)
    - :: is cast operator
    - there is "units" operator - it is something like DATEADD - you can set value and units for that value
    - MDY function converts three integers (day, month, year) to DATE type
    You can find more about Informix SQL in "Guide to SQL: Reference", "Guide to SQL: Syntax" and "Guide to SQL: Tutorial" manuals.
    You can browse Informix manuals or download all Informix manuals for free from IBM site:
    http://publib.boulder.ibm.com/infoce...v115/index.jsp (online)
    http://www.elink.ibmlink.ibm.com/pub...L=SC23-9514-02 (download)

    HTH

  8. #8
    Join Date
    Dec 2008
    Posts
    12
    Again thank you very much.

    I understand statements .

  9. #9
    Join Date
    Dec 2008
    Posts
    12
    Hi again,

    Today the above sql gives this error. What can be the problem TODAY ?

    ------------------------------
    ADDITIONAL INFORMATION:

    ERROR [22008] [Informix][Informix ODBC Driver][Informix]The result of a datetime computation is out of range. (iclit09b.dll)

  10. #10
    Join Date
    Oct 2013
    Posts
    2

    how to get app_name in informix

    Hi,
    I sql we get application name by app_name.but i am not able to get application name using informix database or may i not aware that which function return that.

    Please reply if any one know how to retrieve application name using informix database query.

Posting Permissions

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