CODEWITHSUNDEEP
sqloracle-databasedatetimesplit
Kasun Kumara
Kasun Kumara

Reputation: 11

Splitting date and time in Oracle SQl

I have a date time stored in the format mm/dd/yyyy HH24:mi:ss format(example 01/25/2005 10:10:15 PM). Is there any inbuilt function in Oracle SQL to obtain the date value and time value separately?

I cant use the space to split the 2 values because there are 2 spaces(One between date and time and the other between time and AM/PM)

Upvotes: 0

Views: 5619

Answers (1)

GMB
GMB

Reputation: 222402

If you have a date, and you want to display the date and time portions separately, use to_char():

select to_char(mydate, 'yyyy-mm-dd') datepart, to_char(mydate, 'hh24:mi:ss') timepart
from mytable

You control the format of the string representations with the second argument to to_char().

If you have a string, and you want to split it into two after the first space, then use string functions:

select substr(mystr, 1, instr(mystr, ' ') - 1) datepart, substr(mystr, instr(mystr, ' ') + 1)
from mytable

Upvotes: 2

Related Questions