SQL Server to Oracle Query Conversion

  • roshan.zanwar

    SSChasing Mays

    Points: 617

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    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

  • roshan.zanwar

    SSChasing Mays

    Points: 617

    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?

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    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

  • roshan.zanwar

    SSChasing Mays

    Points: 617

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply