Date Type- First wednesday of a month
The poster asked which Informix 11.5 data type could store recurrence-style values like 'Jan First Monday' or 'April Second Thursday', rather than a resolved calendar date (since 'Sept first Monday' differs each year). Jonathan Leffler replied there is no built-in type: use a string column (e.g. VARCHAR(32)) or a custom UDT, plus your own functions to convert a recurrence string into the next matching date, warning that 'Fifth Friday' usually means 'last Friday'. Steve Black supplied sample SQL using TO_CHAR with %B/%A and a CASE on DAY() to build such a label from a date. The poster accepted these suggestions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I have a requirement to create a column to store data types like 'Jan First Monday, Feb First Friday, April Second Thursday' Please help me which data type will suit this requirement. We are using informix 11.5. Best Regards, Subbu
On Wed, May 23, 2012 at 8:50 AM, M SUBBU <daredevil_subbu@yahoo.co.in>wrote: > I have a requirement to create a column to store data types like 'Jan First > Monday, Feb First Friday, April Second Thursday' > > Please help me which data type will suit this requirement. We are using > Informix 11.5. > There is no built-in data type for that sort of recurrence information, so you can create your own UDT or use a string type (probably VARCHAR(32) or something similar) and then produce functions that can manipulate those strings into 'the next date satisfying this recurrence string on or after this given date' (where the given date might default to TODAY). Don't forget the 'Fifth Friday' notation, which means 'the last Friday' (either 4th or 5th) where 'Fourth Friday' means the fourth Friday, even if there are five Friday's in the month. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04016d8f4f77a704c0b74a6c
Do you mean a specific date including the day of the week or do you mean "the next time January 1st is a Monday"? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 23, 2012 at 11:50 AM, M SUBBU <daredevil_subbu@yahoo.co.in>wrote: > Hi All, > > I have a requirement to create a column to store data types like 'Jan First > Monday, Feb First Friday, April Second Thursday' > > Please help me which data type will suit this requirement. We are using > informix 11.5. > > Best Regards, > Subbu > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340893f8ba4904c0b7a843
Thanks for responding, we dont want to resolve 'Sept first monday 2012' as 9/3/12. As this date may not be first monday in 2013. We need to keep info as 'Sept first monday 2012'.
Appreciate your response, Jonathan.
maybe something like this:
create temp table temp_123 (
a char(30),
b char(30)
);
insert into temp_123select TO_CHAR(TODAY, "%B") || ", " ||
CASE
WHEN DAY(TODAY) <= 7 THEN "First"
WHEN DAY(TODAY) <= 14 THEN "Second"
WHEN DAY(TODAY) <= 21 THEN "Third"
WHEN DAY(TODAY) <= 28 THEN "Fourth"
WHEN DAY(TODAY) > 28 THEN "Fifth"
END || " " || TO_CHAR(TODAY, "%A"),
TO_CHAR(TODAY, "%B %d, %Y")
FROM systables WHERE tabid == 1;
select * from temp_123;
May, Fourth Wednesday May 23, 2012
1 row(s) retrieved.
Steve Black
steven_black@yahoo.com
On Wed, May 23, 2012 at 11:50 AM, M SUBBU <daredevil_subbu@yahoo.co.in>wrote:
> Hi All,
>
> I have a requirement to create a column to store data types like 'Jan First
> Monday, Feb First Friday, April Second Thursday'
>
> Please help me which data type will suit this requirement. We are using
> informix 11.5.
>
> Best Regards,
> Subbu
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Thanks a lot Steve