how oracle stores the date 2

[Zoom1234]

Hi,

In what format does oracle store the date..(dd/mm/yyyy?).
I want to create a table and update the date column to value '1st Jan 1881'. What data type should i define (date or timestamp).

Thanks in advance.

 

[carp]

Oracle stores dates in its own internal format. You can use the TO_CHAR and TO_DATE functions to use virtually any format you wish. In your case, you would use something like

UPDATE my_table 
   SET my_date_column = TO_DATE('1st Jan 1881','DDth Mon YYYY')
 WHERE ....

 

[LKBrwnDBA]

And adding to carp's post, the default date format would be whatever NLS_DATE_FORMAT is set to:

SELECT *
  FROM v$nls_parameters;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Also cehck if the NLS_DATE_FORMAT environment variable is set on the client side.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Zoom1234]

Thanks for the replies.
So if i want to update in format of dd-mmm-yyyy(1-Jan-1881), then my update statment will be

UPDATE my_table 
   SET my_date_column = TO_DATE('1st Jan 1881','dd-mmm-yyyy')
 WHERE ....

Is it right?

 

[LKBrwnDBA]

Wrong the date string must be equal to the date format you use:

UPDATE my_table 
   SET my_date_column = TO_DATE('01-Jan-1881','dd-mmm-yyyy')
 WHERE ....

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

Not quite. If you want to use a different format, then your value and format model must match. So the statement would be

UPDATE my_table 
   SET my_date_column = TO_DATE('1 Jan 1881','dd Mmm yyyy')
 WHERE ....

Remember, Oracle stores dates in its own format. What you are doing here is passing in a character string ('1 Jan 1881') and telling Oracle "here is a character string. I'd like you to treat it as a date, and here is how to map the characters to a date."

On the other hand, if your NLS_DATE_FORMAT is set to 'dd Mmm yyyy', then if you try to update a date column with '1 Jan 1881', it matches the format that Oracle is looking for as a date and no TO_CHAR is required.

A quick way to see what the format your database is set for would be as described above, or simply
SELECT sysdate FROM dual;
Whatever format the date is displayed in would be the format Oracle is using by default.

I would suggest you get a copy of Oracle's SQL users guide online and read the section on dates. It's very useful and informative.

 

[LKBrwnDBA]

Actually what I meant by "the date string must be equal to the date format you use" is that it must be true for the format you use in the TO_DATE() funcion and not necesarily equal to the NLS_DATE_FORMAT.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Zoom1234]

Thanks for the valuable help guys.