Could you please let me know the correct format of CREATE TABLE statement with DATE format.
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,
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
These 2 Users Gave Thanks to durden_tyler For This Post:
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,
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?
Last edited by Poonamol; 10-15-2010 at 05:53 AM..
Reason: code added
i try to set linux date & time in specific format but it keep giving me error
Example :
date "+%d-%m-%C%y %H:%M:%S" -d "19-01-2017 00:05:01"
or
date +"%d-%m-%C%y %H:%M:%S" -d "19-01-2017 00:05:01"
keep giving me this error :
date: invalid date ‘19-01-2017 00:05:01'
Please use CODE tags... (7 Replies)
Hello,
In my shell script, I extract table data from HP Vertica DB into a csv file using vsql -c command. But the problem is the file getting created is in binary format and hence some of the data becomes unreadable which has chinese characters as part of data.
file -i filename.csv - gives... (2 Replies)
Hi Unix Gurus,
I would like to rename several files in a Unix Directory . The filenames can have more than 1 underscore ( _ ) and the last underscore is always followed by a date in the format mmddyyyy. The Extension of the files can be .txt or .pdf or .xls etc and is case insensitive ie... (1 Reply)
My Code
Hi Friends,
I need to get output in table format using unix shell script.For example my server CPU and memory utilization report will come as a mail with ordinary format but i want to make as table format.
Here two output finally we are getting:-
CPU utilization is... (2 Replies)
hi there, I am trying to create a stored procedure that i can pass the table name to and it will create a table with that name. but for some reason it creates with what i have defined as the variable name . In the case of the example below it creates a table called 'tname'
for example
... (6 Replies)
I have a list of dates in the following format: mm/dd/yyyy and want to change these to the MySQL standard format: yyyy-mm-dd.
The dates in the original file may or may not be zero padded, so April is sometimes "04" and other times simply "4".
This is what I use to change the format:
sed -i '' -e... (2 Replies)
I have a comma delimited log file which has the date as MM/DD/YY in the 2nd column, and HH:MM:SS in the 3rd column.
I need to change the date format to YYYY-MM-DD and merge it with the the time HH:MM:SS. How will I got about this?
Sample input
02/27/09,23:52:31
02/27/09,23:52:52... (3 Replies)
I am currently running the following Korn shell script which works fine:
#!/usr/bin/ksh
count=`db2 -x "select count(*) from schema.tablename"`
echo "count"
I would like to add a "where" clause to the 2nd line that would allow me to get a record count of all the records from schema.tablename... (9 Replies)
Hi,
I can use the following command to create a file with some name then underscore and then date appended to it in the format 'DD-MM-YYYY':
touch "newfile_`date '+%d-%m-%Y'`"
But it gives me error when I try with the similar command to create a file with the date format 'DD/MM/YYYY'. I... (4 Replies)
hi,
for reading a cobol indexed file i need to convert "mmddyy" date format to "ccyyddd" format.
i checked the datecalc and other scripts but couldnt modify them to cater to my need:(...
The datecalc gives an output which i believe is the total days till that date, but i want to convert it... (2 Replies)