Re: Getting the last_day in a SQL variable
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: painterman@xxxxxxxxx
- Date: Fri, 10 Jul 2009 12:35:47 +0100
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@xxxxxxx|
| | om |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 10/07/2009 12:20 |
| | Please respond to |
| | painterman |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: oracle-l@xxxxxxxxxxxxx
|
| 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.
Other related posts:
