finding the year of day for a datetime value

[t***@gmail.com]

hello!
does anyone know if there are any functions for finding the year of day for a datetime value? Like, "2015-10-07" is day 280 of the year?
thanks in advance

[Mike Walker]

You can just use date math and subtract Jan 1st from the datetime.

For example:

select DATE(CURRENT) - "1/1/2015" + 1
from systables
where tabid = 1;

(expression)

280

Or to automatically use the current year and a literal datetime:

select DATE("2015-10-07 08:15:27"::DATETIME YEAR TO SECOND) -
MDY(1,1,YEAR(CURRENT)) + 1
from systables
where tabid = 1;

(expression)

280

Mike Walker
Advanced DataTools Corporation


-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of ***@gmail.com
Sent: Wednesday, October 07, 2015 7:48 AM
To: informix-***@iiug.org
Subject: finding the year of day for a datetime value

hello!
does anyone know if there are any functions for finding the year of day for
a datetime value? Like, "2015-10-07" is day 280 of the year?
thanks in advance

[Doug Lawry]

I agree with Mike. This returns 281 today:

CREATE FUNCTION year_day(v_date DATE) RETURNING SMALLINT;
RETURN v_date - MDY(1,1,YEAR(v_date)) + 1;
END FUNCTION;

EXECUTE FUNCTION year_day(CURRENT);

Regards,
Doug Lawry
Oninit Consulting