CODEWITHSUNDEEP
oracle-databasedatetime
Zhe Xin
Zhe Xin

Reputation: 213

Oracle date calculation issue

there is a requirement like below:

string format is : dd hh:mm:ss, this means (days hours:minutes:seconds, day is optional)
now the string will add to value "1/1/4000", so if the incoming value is "00:15:00" the resulting value would be 1/1/4000 00:15:00 (add 15 minutes to 1/1/4000). If the incoming value is 2 00:15:00 then the resulting value would be 1/3/4000 00:15:00 (add 2 days and 15 minutes to 1/1/4000) . If the incoming value is 32 00:15:00 then the resulting value would be 2/1/4000 00:15:00.

so is there any simple method to implement this requirement above?

Upvotes: 0

Views: 350

Answers (1)

Noel
Noel

Reputation: 10525

You can convert your input string to INTERVAL DAY TO SECOND datatype using TO_DSINTERVAL and then add it to your default date. The result will be a date.

date'4000-01-01' + TO_DSINTERVAL('2 23:23:12');

But this requires your input string to be in DD HH:MI:SS format. Since in your input, day is optional, you should append 0 days to the string, in case it isn't present.

Upvotes: 1

Related Questions