Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: convert sql server script to oracle...Plz help

    Gurus, please help me convery this sql server script to oracle..
    ----------

    SET NOCOUNT ON
    DECLARE @INDATE DATETIME, @LOADDATE DATETIME
    SELECT @INDATE='01/01/1954'
    SELECT @LOADDATE=GETDATE()

    WHILE @INDATE<='12/31/2054'
    BEGIN
    INSERT INTO DATE_ADW_COM_DIM(ADATE,CALENDAR_YEAR,CALENDAR_YEAR _NAME,CALENDAR_QTR,CALENDAR_QTR_NAME,
    CALENDAR_MONTH,CALENDAR_MONTH_NAME,CALENDAR_WEEK,C ALENDAR_WEEK_NAME,SOURCE_ID,BATCH_NO,LOADDATE)
    VALUES(@INDATE,DATENAME(YY,@INDATE),DATEPART(YY,@I NDATE),DATENAME(QQ,@INDATE),DATEPART(QQ,@INDATE),
    DATENAME(MM,@INDATE),DATEPART(MM,@INDATE),DATENAME (WK,@INDATE),DATEPART(WK,@INDATE),1,1,@LOADDATE)
    SELECT @INDATE=DATEADD(D,1,@INDATE)
    END
    SET NOCOUNT OFF
    Kishore

  2. #2
    Join Date
    Apr 2004
    Posts
    69
    I found this site useful for converting such scripts online :

    http://www.adventnet.info/license/co.../jsp/index.jsp

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Hi Thanks...
    Looks like this site is no good. I have an Oracle SQL that I used to convert to oracle using their tool. When I wanted to reverse this same sql to oracle,..It is giving errors...
    Kishore

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Change the declarations ... there is no @ ... You select value INTO variable
    with Oracle AND use ; to end statements.

    SQLServer ...
    DECLARE @INDATE DATETIME, @LOADDATE DATETIME
    SELECT @INDATE='01/01/1954'
    SELECT @LOADDATE=GETDATE()

    Oracle ...

    INDATE DATE;
    LOADDATE DATE;

    INDATE := '01-JAN-1954'

    SELECT SYSDATE INTO LOADDATE FROM DUAL;

    HTH
    Gregg

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    Thanks Greg,
    I am mainly looking for conversion of
    -------------------------------
    DATENAME(YY,@INDATE),DATEPART(YY,@I NDATE)
    ------------------------------

    into oracle. Hoe do we handle it?
    Kishore

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Perhaps if you explain what those do for those who don't know SQL Server so well...?

  7. #7
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    You should use this function:

    TO_CHAR(@INDATE, 'YY')

    Format Model Description
    --------------- ------------------------------------------
    CC, SCC Century (S prefixes BC dates with a minus sign)
    YYYY, SYYYY Year (S prefixes BC dates with a minus sign)
    IYYY Year based on ISO standard
    YYY, YY, Y Last three, two or one digits of the year
    IYY, IY, I Last three, two or one digits of the ISO year
    Y,YYY (Four Y's with comma) put a comma in the year (1,995)
    YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign)
    RR Last two digits of year in another century (allows for year 2000)
    BC, AD BC or AD indicator
    B.C., A.D. BC or AD indicators with periods
    Q Numeric quarter of the year (1-4 with Jan-Mar=1)
    MM 2 number month (Jan = 01)
    RM Roman numeral month
    MONTH Name of month spelled out (upper case - month is upper case)
    MON abbreviated name of month (upper case - month is upper case)
    WW Week of the year (1-53)
    IW Week of the year (1-52, 1-53) based on ISO standard
    W Week of month (1-5)
    DDD day of year (1-366) (Don't forget leap years)
    DD day of month (1-31)
    D day of week (1-7)
    DAY Name of day (upper case, day is upper case)
    DY Abbreviated name of day
    J Julian day (Number of days since January 1, 4712 BC)
    AM,PM Meridian indicator
    A.M., P.M. Meridian indicator with periods.
    HH, HH12 Hour of day (0-12)
    HH24 Use 24 hour clock for hours (1-24)
    MI Minute (0-59)
    SS Second (0-60)
    SSSSS (five S's) seconds past midnight. (0-86399)
    None Date must be in the format 'DD-MON-YY';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •