August 28, 2013 at 11:16 pm
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_datedateYearMonthNumberMonthDayOfMonthDayOfWeekNumberDayOfWeekWorkingDay
201001011/1/201020101January16Friday1
201001021/2/201020101January27Saturday0
201001031/3/201020101January31Sunday0
201001041/4/201020101January42Monday1
201001051/5/201020101January53Tuesday1
Thanks in Advance
August 29, 2013 at 2:21 am
The easiest method to do this is to run this query on Oracle and see which functions error out.
Use Google to find the Oracle equivalent. (for example: convert sql server versus oracle).
And check if Oracle supports CTEs 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 30, 2013 at 12:28 am
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?
August 30, 2013 at 1:00 am
And now is the time to go to an Oracle forum, as you're now asking PL/SQL questions 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 30, 2013 at 3:50 am
Got solution for this on Oracle forum.
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;
Thanks for your posts. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply