I have last_update_date column defined as DATE field
I want to get time in milliseconds.
Currently I have:
TO_CHAR(last_update_date,'YYYY-DD-MM hh:mi:ss am')
But I want to get milliseconds as well.
I googled a bit and think DATE fields will not have milliseconds. only TIMESTAMP fields will.
Is there any way to get milliseconds? I do not have option to change data type for the field.
DATE fields on Oracle only store the data down to a second so there is no way to provide anything more precise than that. If you want more precision, you must use another type such as TIMESTAMP.
Here is a link to another SO question regarding Oracle date and time precision.
As RC says, the DATE type only supports a granularity down to the second.
If converting to TIMESTAMP is truly not an option then how about the addition of another numerical column that just holds the milliseconds?
This option would be more cumbersome to deal with than a TIMESTAMP column but it could be workable if converting the type is not possible.