#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    SQL Server to Oracle Conversion


    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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    This is an example you can work from, since I don't have your table(datesraw) I had to create the example using a temp table:
    Code:
    with
    strtorows1 as
      ( select
        regexp_replace(
        q'( 20100101, 20100102, 20100103, 20100104, 20100105,
            20100106, 20100107, 20100108, 20100109, 20100110 )', '[ '''']+') as strtorows from dual ),
    dates as
      ( select regexp_substr( strtorows, '[^,]+', 1, rownum ) as date_of_year
         from  strtorows1
        connect by level <= length( regexp_replace( strtorows, '[^,]+' ) )  + 1 )
    select date_of_year as id_date,
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'mm/dd/yyyy' ) as "date",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'yyyy'       ) as "Year",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'mm'         ) as "MonthNumber",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'Month'      ) as "Month",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'dd'         ) as "DayOfMonth",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'D'          ) as "DayOfWeekNumber",
           to_char( to_date( date_of_year, 'yyyymmdd' ), 'Day'        ) as "DayOfWeek",
           decode( to_char( to_date( date_of_year, 'yyyymmdd' ), 'D' ), 1, 0, 7, 0, 1 ) as "WorkingDay"
     from  dates
    where  to_date( date_of_year, 'yyyymmdd' ) between '1-jan-2010' and '5-jan-2010'
    Output of sql:
    Code:
    ID_DATE  date       Year MonthNumber Month   DayOfMonth DayOfWeekNumber DayOfWeek WorkingDay
    20100101 01/01/2010 2010          01 January         01               6 Friday             1
    20100102 01/02/2010 2010          01 January         02               7 Saturday           0
    20100103 01/03/2010 2010          01 January         03               1 Sunday             0
    20100104 01/04/2010 2010          01 January         04               2 Monday             1
    20100105 01/05/2010 2010          01 January         05               3 Tuesday            1
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    how can i declare variable?


    Thanks for your reply.
    I created query in Oracle with help of your query.
    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?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Thumbs up Solution


    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;

IMN logo majestic logo threadwatch logo seochat tools logo