My Oracle 8i database changes date to the next day after 12:00 PM each day, intead of at 12:00 midnight. I have checked out my NLS Date parameters as well as the Windows 2000 system time, and they're okay. What special setting is required to correct this abnormal change of 'sysdate' after midday?
The first thing to do is to make sure that you are getting the correct time out of the database. The following query can be used to get the database's time:
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') AS current_time FROM dual;Once you have verified that the time is not correct, then you have a few places to look at. First is the NLS_DATE_FORMAT and NLS_TIME_FORMAT initialization parameters. Make sure that those values are correct. You will also want to check that your database's timezone is correct. You can do this with the following query:
SELECT dbtimezone FROM dual;If it is not correct, then you can modify it with a command similar to the following:
ALTER DATABASE SET TIME_ZONE = 'Europe/London';Use the appropriate time zone for your location.
Oracle pulls the time from the database server. It does not store time in the database except for storing a row in a table using the DATE (or TIMESTAMP) datatype. So your next step is to verify that the server is set up correctly. Check the server's time. In your Windows server, start a DOS session and use the 'time' command to verify the time. You will also want to make sure that your server has the correct timezone offset for your location.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.