-
getting first date and last date of previous month in oracle
Hi all,
How can i get first date and last date of previous month.
Please help me.
Thanks
Rajneesh
-
You can use TRUNC function to do that.
select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual
-
hai all
how can i get a patricular day of 6 year before
-
hai all
how can i get all null values of table
-
Please open a separate thread for a separate question. It sounds like you need a tutorial on SQL. Databasejournal has a forum called Structured Query Language for that.
-
Query
Originally Posted by devanasamy
hai all
how can i get a patricular day of 6 year before
solution:
-------------
SELECT sysdate,add_months(sysdate,-12*6),to_char(add_months(sysdate,-12*6),'Day') FROM dual
-
query
Originally Posted by devanasamy
hai all
how can i get all null values of table
solution:
Using IS operator we can fetch null values.
Select * from t1 where c1 is NULL.
-
You can use TRUNC function to do that.
select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual
this works great but how can I make it read 30-APR-2009
-
select to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD-MON-YYYY') "First Day of Last Month",
to_char(trunc(sysdate, 'MM') - 1,'DD-MON-YYYY') "Last Day of Last Month"
from dual
-
I am not an expert with Oracle, can someone please provide me with the proper Where syntax that would return records between "First Day of Last Month" and "Last Day of Last Month".
I tried this but it was not returning any results:
Select * from mytable
Where datefield between trunc(trunc(sysdate,'MM')-1,'MM') and trunc(sysdate,'MM')-1
Thank you.
-
Probably your table (mytable) has no rows!
If you use the dual table, your are guaranteed to have 1 row.
-
Originally Posted by brunobiondo
Probably your table (mytable) has no rows!
If you use the dual table, your are guaranteed to have 1 row.
Sorry, I was not exhaustive.
Your table (mytable) either is empty or it doesn't have any row that mach the specified condition in your query.
-
Originally Posted by brunobiondo
Sorry, I was not exhaustive.
Your table (mytable) either is empty or it doesn't have any row that mach the specified condition in your query.
...and forget I told you to use dual, dual return always 'X' (1 row)
-
getting first date and last date of previous month
for start date of previous month :
DATEADD("m",-1,DATEADD("D",-(DAY(Today())-1),Today()))
for last date of prvious month :
DATEADD("D",-(DAY(Today())),Today())
-
first day of the month can be got using trunc
Trunc to the first of the month:
e.g:
TRUNC (TO_DATE ('12-MAR-1994'), 'MONTH') ==> 01-MAR-1994
TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994
last day of the month can be obtained using function
LAST_DAY('DD-MM-YYYY')
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|