vortilead.blogg.se

The remainder of this article will discuss the DATE, TIMESTAMP and INTERVAL types in more detail. If you want to discount the time component from the comparison, use the TRUNC or ROUND functions to remove it from both sides of the comparison. When doing date or timestamp comparisons, always consider the impact of the time component.Always explicitly perform the conversions with the TO_CHAR, TO_DATE and TO_TIMESTAMP functions, or use ASNI DATE or TIMESTAMP literals. Never rely on implicit conversions of strings to dates, or dates to strings.At exactly midnight the time is 00:00:00. Both DATE and TIMESTAMP types *always* contain a date and time component.When using Oracle DATE or TIMESTAMP values, remember the following simple rules and you will probably avoid most of the common pitfalls. WHERE TRUNC(col1) = TO_DATE('','DD/MM/YYYY') ĪLTER SESSION SET nls_date_format='DD/MM/YYYY' To remedy this, we must either explicitly use the TO_DATE function with a format mask, set the NLS_DATE_FORMAT appropriately, or use an ANSI DATE literal. That string looks perfectly acceptable to me, because I understand the variations in date formats and that looks like a UK representation of "27th April 2013" to me, but the database doesn't know that. Or set the NLS_DATE_FORMAT to the desired format mask.ĪLTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS' Īnother common mistake is when you specify a date as a string. TO_CHAR(col2, 'DD-MON-YYYY HH24:MI:SS') AS col2 SELECT TO_CHAR(col1, 'DD-MON-YYYY HH24:MI:SS') AS col1, To get the full data we have to either explicitly ask for it using the TO_CHAR function with a format mask. You can display the current database, instance and session NLS parameter values using this script. Why has it done this? Because it has used the format mask specified by the NLS_DATE_FORMAT parameter to decide how to implicitly convert the date to a string. SQL*Plus has converted the internal representation of the date into a nice string for us, but it has left out the time component. So both columns contain the same value right?īoth DATE and TIMESTAMP columns contain a time component, which does not match in this case.

INSERT INTO t1 VALUES (TRUNC(SYSDATE), SYSDATE) The following examples use the DATE type, but the issues apply equally to the TIMESTAMP type. In the process, they often miss out very important information that can confuse you if you are not careful. What you see on screen from a query is what's in the database right? Well actually, that is often not the case.Ĭlient tools, like SQL*Plus, convert datetime column values into something much nicer to look at. The vast majority of problems people encounter are because of a misunderstanding about how dates are stored in the database. The way the Oracle database handles datetime values is pretty straightforward, but it seems to confuse many client-side and PL/SQL developers alike.

If you want to hardcode the week start to Saturday and make it independent of NLS settings, then you need to do some more complex date arithmetic, because TRUNC does not accept NLS_TERRITORY as a direct parameter: It will start on Saturday in Arabic countries. The above query adapts to the week definition for a given territory: the week will start on Sunday in US and on Monday in Europe. If you can control the NLS territory setting (or even better: you want to adapt to it), then the simplest solution is to use TRUNC(SYSDATE,'D') and TRUNC(SYSDATE,'D')+6 :Īlter session set nls_territory='saudi arabia' įrom (select date ''+level today from dual connect by level<=10)