Results 1 to 5 of 5

Thread: Oracle - DATEPART

  1. #1
    Join Date
    Apr 2003
    Location
    St. Louis
    Posts
    17

    Oracle - DATEPART

    Is there a DATEPART function for Oracle like there is in SQL? If so, how does it work with the (sysdate)? An example of a "dateweek" statement would be very helpfull.

    Thanks

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    there are many functions / formats that can be used with the sysdate value for returning different values.

    i am not familiar with the DATEPART function in ?SQLServer? please post what you are looking for Oracle to return to you and i will see if i can help.

  3. #3
    Join Date
    Apr 2003
    Location
    St. Louis
    Posts
    17
    What I would like to do is extract the week number from the sysdate. Then, based on the sysdate, I want to see the records with that week number.
    I have a table with a date field, and I want to be able to see the records for today and the rest of the week. I want to be able to do this without having to physically put in today's date. This way, no matter what day it is, I will be able to view records for the rest of the week automatically.
    Hope this clears things up, if not let me know.

    Thanks

  4. #4
    Join Date
    Mar 2003
    Posts
    468
    if you have a date field, in oracle, you can use the TO_CHAR function on the date field with two different date formating options to get the week of the year.

    IW
    Week of year (1-52 or 1-53) based on the ISO standard.
    this means that literal week of the year no matter how many days in the week (first and last weeks of the year).
    for year 2003: days 1-5 is week 1, days 6-12 is week 2 and so on.
    -or-
    WW
    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
    this means day 1-7 is week 1 day 8-14 is week 2 and so on.

    so if you want to evaluate today (sysdate) to the ISO week of the year, just issue the following.
    select to_char(sysdate,'IW') from dual;

  5. #5
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    1

    Getting DATE portion of a Date in ORACLE

    This works for me to extract the date portion from a date in oracle
    to_date( to_char( to_date('1/1/07', 'MM/DD/YY HH:MI:SS AM'), 'MM/DD/YY'), 'MM/DD/YY' )
    Here first I convert the date into a char with only date information and then convert this char into the date.

Posting Permissions

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