2 Replies - 640 Views - Last Post: 26 October 2009 - 06:37 PM Rate Topic: -----

#1 bischmarck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-October 09

Need Help: Getting the last day of month

Post icon  Posted 25 October 2009 - 08:24 PM

I have a query goes like this..
SELECT TSL_DTE,LOGDATE,subdate(TSL_DTE,interval 31 day) TSL_DTE_PM, date_sub(LOGDATE,interval 1 month) LOGDATE_PM
FROM
view_periodsalesdetail
WHERE
TSL_DTE = '2009-06-30';

The problem in this query is, if I inputted a date of month which ends 30 day then the previous month is consist of 31 day it gives an output of 1 month interval which is 2009-05-30( it should be 2009-05-31). What I need is to get the end of day of the each month.
Can anyone could help me in getting out in this problem. :blink:

=============================
| TSL_DTE | TSL_DTE_PM | LOGDATE_PM |
~~~~~~~~~~~~~~~~~~~~~~~~~~
|2009-06-30 |2009-05-30 | 2009-05-30 |
=============================

This post has been edited by bischmarck: 25 October 2009 - 11:20 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Need Help: Getting the last day of month

#2 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 290
  • View blog
  • Posts: 1,524
  • Joined: 07-April 08

Re: Need Help: Getting the last day of month

Posted 26 October 2009 - 06:10 AM

What database are you using as its different between all of them.
Was This Post Helpful? 0
  • +
  • -

#3 bischmarck  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-October 09

Re: Need Help: Getting the last day of month

Posted 26 October 2009 - 06:37 PM

Problem is already solved by me. I'm using a MySQL Server 5.xx as my database.

I simply add this query to the above query given.

DATE_SUB(DATE_ADD(DATE_SUB(TSL_DTE,INTERVAL DAYOFMONTH(TSL_DTE)-1 DAY),INTERVAL 0 MONTH),INTERVAL 1 DAY) PrevMonth

Thanks for the reply.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1