Q
Problem solve Get help with specific problems with your technologies, process and projects.

SYSDATE changing after midday

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


This was last published in August 2003

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close