dates please help

[afaq]

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

afaq

[jolavarria]

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.

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