Code.GeekInterview.com
 
 

Oracle Code Base

 • Compare data in tables of the local Oracle schema
 • Converting Rows to Columns
 • Database Health check
 • Encryt and Decrypt Text messages
 • Function To Convert Amount to Words
 • Oracle Calendar SQL
 • Oracle Database Cold backup Unix
 • Oracle Database Growth Report
 • Partition Management package
 • Report Generator
 • SQL to Find Experience of an Employee
 
Code Samples Oracle
 

Oracle Calendar SQL


Code ResourceAuthor: jamesravid  

Difficulty Level:

Published: 9th Nov 2006   Read: 19383 times  

Filed in: Oracle
Add Comment


 

 

Sponsored Links


 

 

This oracle query returns Calendar for the current year. This Oracle SQL query returns Calendar for the current year.

 


Sample Code
  1.  
  2. SELECT INITCAP(TRIM(TO_CHAR(dat,   'month'))) || ', ' || TO_CHAR(SYSDATE,   'yyyy') MONTH,
  3.   MAX(DECODE(TO_CHAR(dat,   'd'),   2,   TO_CHAR(dat,   'dd'))) mon,
  4.   MAX(DECODE(TO_CHAR(dat,   'd'),   3,   TO_CHAR(dat,   'dd'))) tue,
  5.   MAX(DECODE(TO_CHAR(dat,   'd'),   4,   TO_CHAR(dat,   'dd'))) wed,
  6.   MAX(DECODE(TO_CHAR(dat,   'd'),   5,   TO_CHAR(dat,   'dd'))) thu,
  7.   MAX(DECODE(TO_CHAR(dat,   'd'),   6,   TO_CHAR(dat,   'dd'))) fri,
  8.   MAX(DECODE(TO_CHAR(dat,   'd'),   7,   TO_CHAR(dat,   'dd'))) sat,
  9.   MAX(DECODE(TO_CHAR(dat,   'd'),   1,   TO_CHAR(dat,   'dd'))) sun
  10. FROM
  11.   (SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
  12.      TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
  13.    FROM user_objects
  14.    WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y')))
  15. GROUP BY TO_CHAR(dat,   'month'),
  16.   woy,
  17.   TO_CHAR(dat,   'mm')
  18. ORDER BY TO_CHAR(dat,   'mm'),8;
  19.  
Copyright GeekInterview.com



Next Article: Partition Management package


 

Latest Code Samples

 ♦ Converting Rows to Columns
 ♦ Oracle Database Growth Report
 ♦ Function To Convert Amount to Words
 ♦ Report Generator
 ♦ Database Health check
 ♦ Oracle Database Cold backup Unix
 ♦ SQL to Find Experience of an Employee
 ♦ Oracle Calendar SQL
 ♦ Partition Management package
 ♦ Encryt and Decrypt Text messages
 

Popular Code Samples

 ♦ Function To Convert Amount to Words
 ♦ Converting Rows to Columns
 ♦ Oracle Calendar SQL
 ♦ Database Health check
 ♦ Oracle Database Growth Report
 ♦ Encryt and Decrypt Text messages
 ♦ Partition Management package
 ♦ SQL to Find Experience of an Employee
 ♦ Oracle Database Cold backup Unix
 ♦ Compare data in tables of the local Oracle schema
 

Related Code Samples

 » Oracle Database Growth Report
 » Storing Images and PDF Files In Oracle
 » Oracle Database Cold backup Unix
 » Compare data in tables of the local Oracle schema
 

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    



Comments

this code is not enough to show the calender
I guess you miss some code.
Comment posted by: sargas on 2008-01-29T01:17:32
Hi,

There is one prerequisite for this query. It is "Number of records in user_objects table should have 365 (366 for leap years). Better you can replace user_objects with all_objects since the later one contains more rows than the earlier.

Hope this helps.

Cheers,
James
Comment posted by: James on 2008-02-24T08:46:58
Really good one.
Comment posted by: Keshava M S on 2010-12-29T00:02:32

Popular Coders

# Coder NameHits
1. jamesravid39380
2. krishnaindia200732920
3. Jim.Anderson28665
4. Mohit Sharma20983
5. Beena14152
6. jainsourabh29133
7. Satheesh6002
8. SriramKrishna5009

Active Coders

# Coder NameCodes
1. jamesravid3
2. Mohit Sharma2
3. Satheesh1
4. jainsourabh21
5. SriramKrishna1
6. krishnaindia20071
7. Beena1
8. Jim.Anderson1

Refined Tags