DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2000
    hi there

    i have a date column which contains the date and time. i want to select only the date from the column how do i do it.

    i enetered a select query as follows

    select distinct left(regdate1,9) from user_details.

    it was working fine but now it is not. please help me out.

    cheers and thx in advance


  2. #2
    Join Date
    Oct 2000


    Oracle stores data in a proprietary format. It uses 7 bytes for Data storage and 1 byte is used for the length data.

    The following is the mapping of the oracle's date type

    Byte 1 -> Century
    Byte 2 -> Year
    Byte 3 -> Month
    Byte 4 -> Day
    Byte 5 -> Hour
    Byte 6 -> Minute
    Byte 7 -> Second

    So you can see, Oracle makes no difference between date and time.
    First you should change date mask retrieve from your query, how ? easy, just tell Oracle date format you want to retrieve.

    select distinct to_char(regdate,'dd-mm-yyyy') from user_details

    With this mask you will get 21-08-1992 date format.

    Hope this helps.

Posting Permissions

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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.