asked on

string manipulation in informix tsql

Hello experts,

I have trouble using my TSQL knowledge to create queries on Informix Databases. I want to do a daily grouping. I have a column with whole date time expression including time information. In TSQL, I use a left(datetime,10) grouping and it works just fine. I cant do it with Informix.

2008-05-30 18:39:11.705
2008-06-04 15:33:04.267
2008-06-05 13:16:10.912
2008-06-05 14:40:50.751
2008-06-05 14:51:45.001
2008-06-05 19:00:33.626
2008-06-06 10:29:12.463

What can I do to achieve this? What can I do to replace the 'Left' method?

Thank you in advance

TheLastStraw

Informix has number of inbuilt date functions which you can use and also makes use of positions for grouping and ordering

For example if salesdate in following is defined as a datetime in database this can be converted to a date by using date function

SELECT Area, date(SalesDate), SUM(SalesAmount) FROM Sales
Group By Area, 2
Order by Area, 2

Cannot group or order by function but can by position function appears in.

Also year, week, day functions for manipulating dates.

bozer

ASKER

Thank you, so let's say I have this table:

Date Total
2008-05-30 18:39:11.705 3
2008-06-04 15:33:04.267 4
2008-06-04 18:33:04.267 2
2008-06-05 13:16:10.912 1
2008-06-05 14:40:50.751 5
2008-06-05 14:51:45.001 4
2008-06-05 19:00:33.626 1
2008-06-06 10:29:12.463 7

How do I get this result?

Date Sum
2008-05-30 3
2008-06-04 6
2008-06-05 11
2008-06-06 7

Thank you

ASKER CERTIFIED SOLUTION

TheLastStraw

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bozer

ASKER

Worked like charm, thank you :)