Click here to Skip to main content
14,207,596 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi All,
I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Please help. Following is the query in SQL server.
DECLARE @StartYear AS INT = 2010; 
DECLARE @EndYear AS INT = 2014; 

WITH 
years 
     AS (SELECT YYYY = @StartYear 
         UNION ALL 
         SELECT yyyy + 1 
         FROM   years 
         WHERE  yyyy < @EndYear)
,months 
     AS (SELECT MM = 1 
         UNION ALL 
         SELECT mm + 1 
         FROM   months 
         WHERE  mm < 12)
,days 
     AS (SELECT DD = 1 
         UNION ALL 
         SELECT dd + 1 
         FROM   days 
         WHERE  dd < 31)
,datesraw 
     AS (SELECT YYYY = yyyy, 
                MM = mm, 
                DD = dd, 
                ID_Date = yyyy * 10000 + mm * 100 + dd, 
                Date = CASE 
                         WHEN Isdate(yyyy * 10000 + mm * 100 + dd) = 1 THEN Cast
                          ( Cast(yyyy * 10000 + mm * 100 + dd AS VARCHAR) AS DATE) 
                         ELSE NULL 
                       END 
         FROM   years 
                CROSS JOIN months 
                CROSS JOIN days 
         WHERE  Isdate(yyyy * 10000 + mm * 100 + dd) = 1) 
SELECT d.id_date, 
       d.date, 
       [Year] = Year(d.date), 
       MonthNumber = Month(d.date), 
       [Month] = Datename(month, d.date), 
       DayOfMonth = Day(d.date), 
       DayOfWeekNumber = Datepart(dw, d.date), 
       [DayOfWeek] = Datename(dw, d.date), 
       WorkingDay = Cast(CASE Datepart(dw, d.date) 
                           WHEN 1 THEN 0 -- Sunday 
                           WHEN 7 THEN 0 -- Saturday 
                           ELSE 1 -- Might lookup for a holidays table here 
                         END AS BIT) 
FROM   datesraw d 
ORDER  BY d.date

Which Gives output in following format.
id_date	date	Year	MonthNumber	Month	DayOfMonth	DayOfWeekNumber	DayOfWeek	WorkingDay
20100101	1/1/2010	2010	1	January	1	6	Friday	1
20100102	1/2/2010	2010	1	January	2	7	Saturday	0
20100103	1/3/2010	2010	1	January	3	1	Sunday	0
20100104	1/4/2010	2010	1	January	4	2	Monday	1
20100105	1/5/2010	2010	1	January	5	3	Tuesday	1


Thanks in Advance
Posted
Updated 28-Aug-13 19:19pm
v2
Comments
Thanks7872 29-Aug-13 1:13am
   
i am not that good with writing queries in Oracle. so you thought post it here and some one will work on behalf of you,right?
Prasad Khandekar 29-Aug-13 1:26am
   
Have you tried Oracle SQL Developer? (http://www.oracle.com/technetwork/products/migration/connect-sqlserver-1945229.html)
Please give it a try.

Regards,
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Thanks for your suggestion.
Somehow i managed to write query in Oracle.
Following is my query.

WITH years AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL <= (select round( SYSDATE-to_date('2010-01-01','YYYY-MM-DD')) from dual))
Select
to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') * 10000 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'mm') * 100 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As Date_ID
,to_date('2010-01-01','YYYY-MM-DD')-1 +rn As "Date"
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') As Year
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'MM') As Month_Number
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Month') As Month
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As DayOfMonth
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Day') As DayOfWeek
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'D') As DayOfWeekNo
,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Q') As Quarter
From years;



If you run this query you will see my expected output.

Now my concern is i don't want to hard code date. In above query i have hard coded the date '2010-01-01'.
Can we somehow use it from variable?
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Got solution.

var start_date varchar2(10)
exec :start_date := '2010-01-01'

WITH years AS (
SELECT to_date(:start_date,'YYYY-MM-DD') -1 +level dt
FROM dual
CONNECT BY LEVEL <= (select round( SYSDATE-to_date(:start_date,'YYYY-MM-DD')) from dual)
)
Select
to_char(dt ,'yyyy') * 10000 + to_char(dt,'mm') * 100 + to_char(dt,'dd') As Date_ID
,dt As "Date"
,to_char(dt ,'yyyy') As Year
,to_char(dt,'MM') As Month_Number
,to_char(dt,'Month') As Month
,to_char(dt,'dd') As DayOfMonth
,to_char(dt,'Day') As DayOfWeek
,to_char(dt,'D') As DayOfWeekNo
,to_char(dt,'Q') As Quarter
From years;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190612.1 | Last Updated 30 Aug 2013
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100