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
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.