![]() in SQL Developer, from Tools->Preferences->Database->NLS). (Most clients have a way to let you set the defaults so you don't have to do the same alter commands every time you connect e.g. As you can see, it's easy to change those to modify the output. When you do need to display it, if the display format is important to you then use to_char() with an explicit format mask - do not assume that anyone else running your queries will have the same NLS settings. In your table make the data type timestamp (or timestamp with time zone or timestamp with local time zone), and only worry about formatting the value as a string for presentation to the end user, at the last possible moment. Which isn't entirely relevant if you're really talking about storing timestamps in a table, but shows that there are variations. You can see the timezone and fraction seconds with your default timestamp_tz format: select systimestamp from dual Īnd change it with a different alter: alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM' You are losing the time zone portion, and any fractional seconds which you could also do with a cast: select cast(systimestamp as timestamp(0)) from dual Select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual Īnd I can change it see what you want to see: alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS' īut all you are doing is converting from a timestamp with time zone (which is what systimestamp is) to a string and then back to a timestamp. Select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') from dual I can set my session up to match what you are seeing: alter session set nls_timestamp_format = 'DD-MON-RR HH.MI.SS.FF AM' When you query a timestamp it is displayed using your client's NLS settings, unless you have a client that overrides those. As said, timestamps (and dates) are not stored in a format you would recognise Oracle uses an internal representation that you never really need to know about or examine (but it is documented if you're interested in that sort of thing).
0 Comments
Leave a Reply. |