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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy