Code.GeekInterview.com
  I am new, Sign me up!
 
 

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: 7058 times  

Filed in: Oracle
Add Comment


 


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

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

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 Comment


Members Please Login

Name:  Email: (Optional. Used for Notification)

Title:
 
Comment:
Validation Code: <=>  (Enter this code in text box)





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

Popular Coders

# Coder NameHits
1. jamesravid15859
2. Jim.Anderson8587
3. krishnaindia20077905
4. Mohit Sharma6552
5. Beena5207
6. jainsourabh23990
7. SriramKrishna1802
8. Satheesh1737

Active Coders

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

Refined Tags

 

Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact  

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape