Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    10

    Unanswered: RR and YY functions

    Current year Date from SQL Interpreted by RR Interpreted by YY
    1 1994 27-OCT-95 1995 1995
    2 1994 27-OCT-10 2010 1910
    3 2013 27-OCT-95 1995 2095
    4 2013 27-OCT-10 2010 2010

    These interpreted values are calculated based on certain rules. These rules are used to receive the interpreted RR and YY values in the above table.

    If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

    That is:

    If the specified two-digit year is 00 to 49, then:

    - If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.


    - If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.


    If the specified two-digit year is 50 to 99, then:

    - If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.


    - If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    TO_DATE() isn't a Microsoft SQL Server function. Because of the manipulation you're requesting for the RR and YY functions, the sytnax will be very database engine specific (Microsoft SQL Server, DB2, Oracle, etc. use very different syntax to define functions and manipulate strings).

    Which database engine did you expect to use for these functions?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    10
    Oracle 11g. I feel like until I get used to the terminology and reading the phrases correctly I'm going to be struggling with my training.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    The ANSI/ISO Standard uses ISO-8601 as the only date format. This is 'yyyy-mm-dd HH:MM:ss.ssss' and has to be acceptable to any SQL product (tho some will make it harder than others).

    What you are doing is the kludging we did for the "Y2K Crisis" to repair a bad desiogn in COBOL. You mght want to repair the damage in the front end and not the Database

  5. #5
    Join Date
    Jan 2013
    Posts
    10
    Thank you for the reply. I don't know what you are talking about, but thank you, lol

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    I am old and write ANSI Standards

    Google ISO-8601 Standards. They are embedded in many other standards and you should know them if you are going to write SQL.

    Back in the days of COBOL, we left off the century (1900) from dates on punch cards and magentic tape files. We got the same problems that you now have back in 2000,and has to do just what you are doing. Only the pivot point varied

    Oracle's display format is the worst choice in any SQL; it depends on English, it cannot sort, nobody else uses it, etc.

Posting Permissions

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