Informix syntax error

denizcaglar · #1 (**permalink**) 12-17-08, 06:35

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

ibm.ids · #2 (**permalink**) 12-17-08, 07:52

I don't speak SQL server.
What do you want to do?

denizcaglar · #3 (**permalink**) 12-17-08, 08:27

I want to take the first and the last day's date of the previous month.

ibm.ids · #4 (**permalink**) 12-17-08, 08:31

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:

Quote:

I want to take the first and the last day's date of the previous month.

ibm.ids · #5 (**permalink**) 12-17-08, 08:40

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

denizcaglar · #6 (**permalink**) 12-17-08, 08:45

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

ibm.ids · #7 (**permalink**) 12-17-08, 15:13

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

denizcaglar · #8 (**permalink**) 12-17-08, 16:40

Again thank you very much.

I understand statements

.

denizcaglar · #9 (**permalink**) 12-31-08, 02:28

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech