Hi,
I want to execute query for that i require end date of the month,

suppose if the month is April then it should return 30
if March then in should return 31
Is there any inbuilt function for that which give the end date of the month


Regards
Prateek.Jain

"Prateek Jain" <prateekvjain@gmail.com> wrote in message
news:mailman.139.1147157764.848.informix-list@iiug.org...
> Hi,
> I want to execute query for that i require end date of the month,
>
> suppose if the month is April then it should return 30
> if March then in should return 31
> Is there any inbuilt function for that which give the end date of the month
>
> Regards
> Prateek.Jain

DATE( MDY(MONTH(TODAY),1,YEAR(TODAY)) + (1 UNITS MONTH) - (1 UNITS DAY) )

--
Regards,
Doug Lawry
www.douglawry.webhop.org

Prateek Jain wrote:
> Hi,
> I want to execute query for that i require end date of the month,
>
> suppose if the month is April then it should return 30
> if March then in should return 31
> Is there any inbuilt function for that which give the end date of the month
>
>
> Regards
> Prateek.Jain

Build yourself a little case statement. Here's one in shell-script, you can
convert it easily enough into Informix-syntax. It also is a good way to get
the end-of-month without having to run a select statement, resulting in faster
performance without potential delay(s).

DAY=`date +%d`
MON=`date +%m`
YEAR=`date +%Y`
DOW=`date +%w`

IS_LEAP_YEAR=0
IS_LEAP_YEAR=`cal 2 $YEAR | grep 29`
case $MON in
01) EOM=31
;;

02)
if [ $IS_LEAP_YEAR ] ; then
EOM=29
else
EOM=28
fi
;;

03) EOM=31
;;

04) EOM=30
;;

05) EOM=31
;;

06) EOM=30
;;

07) EOM=31
;;

08) EOM=31
;;

09) EOM=30
;;

10) EOM=31
;;

11) EOM=30
;;

12) EOM=31
;;

esac

Prateek Jain wrote:
> Hi,
> I want to execute query for that i require end date of the month,
>
> suppose if the month is April then it should return 30
> if March then in should return 31
> Is there any inbuilt function for that which give the end date of the month

Download June Hunt's presentation at the IIUG/IDUG User Conference last week
on SQL from the IIUG Web site. She deals with several of these type of
queries and time calculations there.

You all should have been there!

Art S. Kagel

Prateek Jain wrote:
> Hi,
> I want to execute query for that i require end date of the month,
>
> suppose if the month is April then it should return 30
> if March then in should return 31
> Is there any inbuilt function for that which give the end date of the month

Download June Hunt's presentation at the IIUG/IDUG User Conference last week
on SQL from the IIUG Web site. She deals with several of these type of
queries and time calculations there.

You all should have been there!

Art S. Kagel