Programming

Hello,

Could you please let me know the correct format of CREATE TABLE statement with DATE format.

Code:

CREATE TABLE EMP_TABLE1 (
   NAME VARCHAR(6) PRIMARY KEY,
   ADDRESS VARCHAR(6),
   BIRTH_DATE  DATE
);

I want BIRTH_DATE to be in "YYYYMMDDHHMISS" format.
How we can create table with this format of DATE in oracle?
Thanks a lot in advance.

Oracle uses an internal date/time representation, and the way it's displayed is determined by the sessions NLS_DATE_FORMAT setting, eg ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS';

But how can i use the same in CREATE TABLE statement,
like,

Code:

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS';
CREATE TABLE EMP_TABLE1 (
   NAME VARCHAR(6) PRIMARY KEY,
   ADDRESS VARCHAR(6),
   BIRTH_DATE  DATE
);

So the DATE will be in YYYYMMDDHHMISS format?

I think you are confused by pludi's response.

Oracle uses an internal date/time representation to store dates, timestamps etc.

This means you have no control over that internal representation. You wouldn't care about it either.

There are three things here -

(a) Creation : Set DATE as the datatype of the column if you want to store dates in there. Set TIMESTAMP as the datatype of the column if you want to store timestamps in there. You won't care about the format at this point.
Oracle executes your CREATE TABLE statement, and will convert your input value into its own internal representation.

(b) Input : You will have to input dates into a DATE column. You use the keyword "SYSDATE" if you want to store date and time as of now. Otherwise, you convert a string to date using TO_DATE function and insert that value. Oracle provides some laxity over here, for it can convert some of the strings to dates implicitly.

(c) Output : This is where you want to format Oracle's internal representation to suit your need. pludi's "ALTER SESSION" statement will help you format the date fetched from Oracle to the (string) format "YYYYMMDDHH24MISS". Again, you could use TO_CHAR function to convert from date to string. Of course, that would change just the column values you are working on, rather than every date in your session.

It's like a CD player - your input is your CD, it's output is the audio output that you hear.

- Do you care about how its components are arranged internally ? No.
- Do you care how it reads your CD ? No.
- Could you change its internal structure ? You might, but I'm sure you do not want to.

All you want to do is input a CD that is of correct format and you'll get your output.

Same is the case with Oracle.

- Can you force a DATE column in a table to be of YYYYMMDDHHMISS format ?
No. Never.
Oracle stores the date in its own internal format, and you cannot change that, period.

- Can you force a DATE fetched from Oracle to be in YYYYMMDDHHMISS format ?
Yes, you can. Use "ALTER SESSION" command or "TO_CHAR" function. The output will be a string in each case.

- Can you insert a DATE into Oracle in YYYYMMDDHHMISS format ?
Nope.
You can convert a string to a date and store that date in an Oracle column. If you know your string to be in YYYYMMDDHHMISS format, then you can use that format for the conversion, but the output will be a DATE and that's what is going into Oracle. If your string is in some other format, then use the corresponding format for the conversion.

HTH,
tyler_durden

Than you so much for this valuable suggesion and thanks for your time.

---------- Post updated at 03:47 AM ---------- Previous update was at 03:43 AM ----------

Thanks a lot.

What is difference between TO_DATE() and TO_CHAR() functions.
While inserting data into table we are using it. Like,

Code:

INSERT INTO TABLE1 (NAME, ADDRESS, BIRTHDATE )
		VALUES (	'$Name', 	'$Address', TO_DATE ('$BirthDate' , 'YYYYMMDDHH12MISS') );

If I want to use variable value to be put into table in INSERT INTO statement then which function is better to use for DATE / TIMESTAMP format? and How?