Re: Getting the last_day in a SQL variable

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Fri, 10 Jul 2009 12:35:47 +0100
Message-ID: <OF52FF2FE1.FCE00BA1-ON802575EF.003F2807-802575EF.003FB36E_at_ons.gsi.gov.uk>

Well last_day works on a date data type so what you can do is

select count(distinct(ppsn))
from   award_result
where  to_date(date_awarded, 'DD-MON-YY') >= to_date('01-&month-&year','dd-mon-yy')
and    to_date(date_awarded, 'DD-MON-YY') <= last_day(to_date('01-&month-&year','dd-mon-yy'))

alternatvely as you are already applying a function to date_awarded you could use

select count(distinct(ppsn))
from   award_result
where  trunc(to_date(date_awarded, 'DD-MON-YY'),'MM') = to_date('01-&month-&year','dd-mon-yy')

Cheers,

Ian

|---------+----------------------------->


|         |           painterman_at_gmail.c|

|         |           om                |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           10/07/2009 12:20  |

|         |           Please respond to |

|         |           painterman        |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       oracle-l_at_freelists.org                                                                       |
  |       cc:                                                                                                    |
  |       Subject:  Getting the last_day in a SQL variable                                                       |
  >--------------------------------------------------------------------------------------------------------------|

Hi guys,

I'm trying to do a sql script (please see below) but i cant figure out how to get in a variable the value of the LAST_DAY in a month... any tips?

accept month prompt 'Enter Month [MON]:'; accept year prompt 'Enter Year [YY]:';

def lastday=LAST_DAY(&month);

select count(distinct(ppsn))
from   award_result
where  to_date(date_awarded, 'DD-MON-YY') >= '01-&month-&year' and    to_date(date_awarded, 'DD-MON-YY') <= '&lastday-&month-&year';

Thanks!!

David

This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In case of problems, please call your organisation’s IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

---

Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications

---

Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics

---

The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Fri Jul 10 2009 - 06:35:47 CDT