CODEWITHSUNDEEP
sqloracle-databasedate
santhosha
santhosha

Reputation: 629

Oracle Date Conversion

I have a query field in MS access 2010 which will convert the Date for Asia Pacific Region.

My query field looks like this.This is a Calculated field. 'Employee List' is the Table Name and 'Region'&'Created' are the field Names here.Here 0.3958 is added to align the date of APAC region with remaining two other regions.

Report date:

CDate(Int(IIf([Employee List]![Region]="APAC",[Manual_list]![Created]+0.3958,[Manual_list]![Created])))

Now i am trying to achieve this result in Oracle but i am not that familiar in SQL. Could any one help me to have this result in oracle.

Thanks

Upvotes: 1

Views: 102

Answers (2)

Paul Michaels
Paul Michaels

Reputation: 16685

In ORACLE, you can alter the timezone of the database:

ALTER database SET TIME_ZONE = '09:30';

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26333

Assuming that adding .3958 to a date in Access adds 9.5 hours (.3958 * 24 is approximately 9.5), the way to add 9.5 hours in Oracle is:

your_date + INTERVAL '570' MINUTE

That's because 9.5 hours = 570 minutes. You can also do this, which might read better; your choice:

your_date + INTERVAL '9' HOUR + INTERVAL '30' MINUTE

To add the IF logic, use the CASE statement. Putting it all together, you get something like this:

SELECT
  CASE
    WHEN Region = 'APAC' THEN Created + INTERVAL '570' MINUTE
    ELSE Created
  END
FROM your_table
... and so on

Upvotes: 3

Related Questions