We help IT Professionals succeed at work.

Oracle Date and Date with timestamp question

Dargie
Dargie asked
on
I have create a unix unload script that dynamically unloads to a flat file.  My problem is that two types of date values are used. One 20010919 and another 2001-09-19 12:00 or date and date timestamp.  

What is the best way of determining when to pass the format of the time stamp?  It is my understandinf that the DATETIMPSTAMP data type does not exist in oracle.

An excerpt from my script is shown below

sqlplus -s << EOF > unload.tmp
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||''|''||'
from user_tab_columns where table_name = upper('$TableName')
and column_id != (select max(column_id)
from user_tab_columns where table_name = upper('$TableName'))
/
select lower(column_name)||'||''|'''
from user_tab_columns where table_name = upper('$TableName')
and column_id = (select max(column_id)
from user_tab_columns where table_name = upper('$TableName'))
/
exit
EOF
echo "from $TableName" >> unload.tmp

echo "/" >> unload.tmp
echo "exit" >> unload.tmp

sqlplus -s << EOF > $DataFileName
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start unload.tmp
exit
EOF
Comment
Watch Question

Commented:
I'm a little unsure what it is that you are actually trying to do. But the basic point is that Oracles date format supports date and time.

To output it in any format that you fanch you can use the TO_CHAR function.

The format is TO_CHAR(column_name, formatstring)

eg TO_CHAR(mycol,'DD-MM-RR')

might return 23-01-01

format string can contain a variety of specifiers including

RRRR - 4 digit year (YK2 Complient)
MM   - 2 Digit Month
DD   - 2 Digit Day
MONTH - Month name
MON - First 3 characters of the month name
HH - Hours in 12 hour clock
HH24 - Hours in 24 hour clock
MI - Minutes
SS - Seconds
SSSSS - Seconds since midnight.

So return 20010919 you would need to use a format string of
'RRRRMMDD'
or get 2001-09-19 12:00 with

'RRRR-MM-DD HH24:MI'

HTH

Steve

Author

Commented:
Let me clarify a bit.  My script basically unloads all data from a table into a flat file.

My concern is that two different date formats are contained in a table ie date1 has 20010919 and date2 has 2001-09-19 12:56.  When I run the above script I get 2001091 for date1 and 20010919 for date2.  Is there any way to tell which columns contain date and timestamp as I have to unload may tables with different schemas.

If worse come to worse I gues I can use sed or awk to replace the column name with a precision for the date

Commented:
say we have columns order_date that contains only a date and order_time which contains the date and time

compare the columns to a truncation of themselves

TRUNC(order_date) will always equal order_date
TRUNC(order_time) will not equal order_time

we can now build this comparison into a DECODE statement to determine what format mask to use

SELECT DECODE(date_col, TRUNC(date_col), TO_CHAR(date_col, 'yyyyddmm'), TO_CHAR(date_col, 'yyyyddmmhh24miss')) FROM <<<table>>>;

This is what you appear to want?
Commented:
Your date columns must be of VARCHAR2 or CHAR datatypes for them to have different formats; since oracle stores all dates in the same format (set by NLS_DATE_FORMAT).

In order to differentiate you could use DECODE  if Date1 is always in 'YYYYMMDD' format and Date2 is always in 'YYYY-MM-DD HH24:MI'  

e.g.   select Date_Col,
                      DECODE(SUBSTR(Date_col,5,1) , '-', TO_CHAR(to_date(Date_col, 'YYYY-MM-DD HH24:MI'),
                                         'YYYYMMDD'), Date_col ) Conv_Date
                      from test_date

This is what the results are

DATE_COL                  CONV_DATE
--------------------            --------------------
20010919                    20010919
2001-09-19 12:56      20010919

OR

if you have a date '20010919 12:56' then it won't work BUT you could use:

select DATE_COL,
            DECODE(LENGTH(Date_col), 8, Date_Col,
                              TO_CHAR(TO_DATE(TRANSLATE(Date_col,'1234567890 -:','1234567890' ),
                                                   'YYYYMMDDHHMI'),'YYYYMMDD') ) Conv_date
     from test_date

Results
DATE_COL                 CONV_DATE
--------------------            -----------------
20010919                    20010919
2001-09-19 12:56      20010919
20010919 12:56         20010919

Commented:
My comment above assumes that all dates are of the datatype DATE

Some applications truncate off the time part of the date on input. This allows better searching e.g. WHERE value_date = par_date rather than WHERE value_date LIKE par_date

Commented:
Yes it is true the you can truncate the time off, but if you convert the date to char using
TO_CHAR(date_col, 'YYYY-MM-DD HH24:MI') what you get for those truncate dates is
2001-09-19 00:00 and for those without truncated dates you get
2001-09-19 13:30

Commented:
Which is why you use a DECODE statement to work out which format mask you need to use, as shown in my example

Commented:
include an alter session in your script before downloading the data from the second table. Remove the '24' in the format if you are using 24-hour format which will result in the AM/PM suffixed to the date. If you end the session after that it is fine, otherwise again do ALTER SESSION with a different format if you need a change in the data format. HTH

select lower(column_name)||'||''|''||'
from user_tab_columns where table_name = upper('$TableName')
and column_id != (select max(column_id)
from user_tab_columns where table_name = upper('$TableName'))
/
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'
/
select lower(column_name)||'||''|'''
from user_tab_columns where table_name = upper('$TableName')
and column_id = (select max(column_id)
from user_tab_columns where table_name = upper('$TableName'))
/

Result of setting the nls_format in sqlplus:

PIND1:PINADM>select sysdate from dual;

SYSDATE
----------------
2001-09-25 17:05

SP

Explore More ContentExplore courses, solutions, and other research materials related to this topic.