Solved

Date Manipulation in PL/SQL

Posted on 2004-08-30
4
1,938 Views
Last Modified: 2012-05-05
Hi,

I'm trying to wright a script to take an oracle date and substract both 1 second and 2 seconds from it.

Here's my solution which works on my test database but not on production.

to_char(lAvailableCycleIterCurRec.END_DATE_TIME-1.1574074074074074074074074074074e-5,'yyyymmddhh24miss'));

Any help much appreciated.

Thanks,

Derek.
0
Comment
Question by:ruamire
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 130 total points
ID: 11929590
What you've done is the equivalent to:

to_char(lAvailableCycleIterCurRec.END_DATE_TIME - ( 1 / ( 24 * 60 * 60 ) ), 'yyyymmddhh24miss' )

which is correct... what (if any) error is being produced on production, in comparision to Test, or describe what differences there are...
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 120 total points
ID: 11929912
Hi Derek,

what is the problem that u encounter on production ? Do you get an error ? if so, what error? If you dont get the desired result, then what is the result that you get ?

Have you checked whether the data type of the column - lAvailableCycleIterCurRec.END_DATE_TIME is same on TEST and PRODUCTION databases ?

Cheers !
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11929936
Can you try the following :

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS';

Select lAvailableCycleIterCurRec.END_DATE_TIME,
         lAvailableCycleIterCurRec.END_DATE_TIME-1.1574074074074074074074074074074e-5
From TableA;

Select sysdate, sysdate - 1.1574074074074074074074074074074e-5 from dual;

Hope this helps !

0
 
LVL 1

Author Comment

by:ruamire
ID: 11930029
I've changed it now to

to_date(lAvailableCycleIterCurRec.END_DATE_TIME, 'yyyymmddhh24miss' ) - (1/24/60/60));

The problem was that new date format came in, previously only the date was specified, now they specified date and time and our conversion was only 'yyyymmddhhmiss', not 'yyyymmddhh24miss'.

It caused problems all over the place!

Thanks for you help

Derek.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 68
oracle DR - data guard failover. 18 61
sum of columns in a row in oracle 3 44
Migration from sql server to oracle 5 38
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question