Thursday, September 02, 2010

PL/SQL Date Calculations

Recently, I needed to do some Oracle PL/SQL / SQL Date range manipulation. I thought that I would share what I used after a quick web search returns some overly verbose results based on conversions using TO_CHAR.
Queries:

SELECT 
   SYSDATE "Today",
   TRUNC(SYSDATE) "Start of Day",
   TRUNC(SYSDATE+1) - 1/(24*60*60) "End of Day (EOD)",
   TRUNC(SYSDATE,'MM') "First Day of the Month",
   TRUNC(SYSDATE,'YEAR') "First Day of the Year"
FROM 
   DUAL
Results: (Formated for readability)

Today                   9/2/2010 5:17:51 PM
Start of Day            9/2/2010
End of Day              9/2/2010 11:59:59 PM
First Day of the Month  9/1/2010
First Day of the Year   1/1/2010

No comments: