CODEWITHSUNDEEP
sqlsql-serverstored-proceduresgroup-byinformix
Aossey
Aossey

Reputation: 935

MS SQL To Informix 11 - Group By converted datetime

I am attempting to move a stored procedure from Microsoft SQL Server 2000 to Informix 11. The original SP contains a final select statement with a GROUP BY statement that includes a converted datetime:

group by convert(varchar(8), c.startDateTime, 1)

When I convert this to Informix syntax I get a syntax error at run time:

GROUP BY (c.startDateTime::DATETIME YEAR TO DAY)::VARCHAR(10)

Can anyone please point me to how, if possible, this can be done in Informix? If this is not possible, which I suspect, how would you typically handle this in the overall query?

Upvotes: 2

Views: 759

Answers (3)

Jason Harris
Jason Harris

Reputation: 11

In Informix, it is generally not required to convert datetime fields to character strings to manipulate them. Instead, use the extend function.

Example, if c.StartDateTime is defined as a datetime year to second e.g. 2012-10-28 23:00:00 and you want just the date portion, use extend (c.StartDateTime, year to day). This will return 2012-10-28.

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

Are you converting to Informix syntax for dates to varchar in mm/dd/yyyy (mssql style=1) as

TO_CHAR(c.StartDateTime,"%m/%d/%iY")

???

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

I think you need to convert from this:

SELECT a, b, c 

FROM  ...

GROUP BY (c.startDateTime::DATETIME YEAR TO DAY)::VARCHAR(10) ;

to something like:

SELECT a, b, c, 
    (c.startDateTime::DATETIME YEAR TO DAY)::VARCHAR(10) AS d

FROM  ...

GROUP BY 4 ;        --- meaning: the 4th column in the SELECT clause

Upvotes: 3

Related Questions