Last Date of previous month
Posted in 2003
Navdeep Virk needed the last date of the previous month in SQL for an ACE report. rkusenet suggested taking the first of the month and subtracting one day. Michael Krzepkowski gave mdy(month(today), 1, year(today)) - 1, and TBP gave a select using date of the first of the month minus 1 units day. Solved.
Topics: SQL Development & Query Writing
List , I want to obtain the Last Date of previous Month ie 09/30/2003 ,if report is run in Oct . and 10/31/2003 when report is run in Nov. I need a Sql solution as I will be using an ACE report . --Message for rkusenet -Ravi , you email Id please ,send it to this address . TIA Navdeep _________________________________________________________________ Concerned that messages may bounce because your Hotmail account is over limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es sending to informix-list
"navdeep virk" <nvirk@msn.com> wrote in message news:booddc$b6q$1@terabinaries.xmission.com... > > List , > > I want to obtain the Last Date of previous Month ie 09/30/2003 ,if report is > run in Oct . and 10/31/2003 when report is run in Nov. I need a Sql > solution as I will be using an ACE report . > the best way I use is to take the first of next month and then decrease that date by 1. I can't tell off hand how it can be done by a sql statement inside ACE. May be you can write a function to achieve that and call that function thru the SQL.
navdeep virk wrote: >List , > >I want to obtain the Last Date of previous Month ie 09/30/2003 ,if report is >run in Oct . and 10/31/2003 when report is run in Nov. I need a Sql >solution as I will be using an ACE report . > >--Message for rkusenet -Ravi , you email Id please ,send it to this address >. > >TIA > >Navdeep > >_________________________________________________________________ >Concerned that messages may bounce because your Hotmail account is over >limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es > >sending to informix-list > > mdy(month(today), 1,year(today)) - 1 HTH Michael
rkusenet wrote: > "navdeep virk" <nvirk@msn.com> wrote in message news:booddc$b6q$1@terabinaries.xmission.com... > >>List , >> >>I want to obtain the Last Date of previous Month ie 09/30/2003 ,if report is >>run in Oct . and 10/31/2003 when report is run in Nov. I need a Sql >>solution as I will be using an ACE report . >> > > > the best way I use is to take the first of next month and then decrease that > date by 1. I can't tell off hand how it can be done by a sql statement inside ACE. > May be you can write a function to achieve that and call that function thru the > SQL. > > > > select date ("1/"||month(today)||"/"||year(today)) - 1 units day from systables where tabid = 1;