Import Date Format mmddyyyy

Question

Hello,

I have to import dates into oracle with this type of format

03JUN2008

The only example I've seen this done before has dashes and this does not.

Should I just bring it in as varchar and manipulate or is there another way?

Thanks

Answer 1

I don't know if i quite catch what you mean but i think that you can do this simply with TO_DATE, examples and details here

http://www.techonthenet.com/oracle/functions/to_date.php

Answer 2

If its a date, then store it as a date, don't mess about with storing it as a varchar or any nonsense like that.

How exactly are you planning to "import" the data?

If you are just using sql statements, then use TO_DATE

MWATSON@:> create table date_test ( x date );

Table created.

MWATSON@:> insert into date_test values ( to_date('03JUN2008','DDMONYYYY') );

1 row created.

MWATSON@:> select * from date_test;

X
-----------
03-Jun-2008

1 row selected.

MWATSON@DEV2:>

If you are importing via SQL*Loader you can specify the date format in the control file

> cat date_test.ldr 
LOAD DATA
INFILE *
APPEND
INTO TABLE date_test
FIELDS TERMINATED BY '|'
(x DATE 'DDMONYYYY')
BEGINDATA
01JAN1999
> sqlldr mwatson control=date_test.ldr
....
Commit point reached - logical record count 1
> sqlplus 
....
MWATSON:> select * from date_test;

X
-----------
03-Jun-2008
01-Jan-1999

2 rows selected.

MWATSON@:>

Answer 3

In order to get a good answer, you have to think ahead of time what you are going to do with those dates in the context of how Oracle stores dates internally.

For example if you do

to_date('03JUN2008','DDMONYYYY')

as Matthew suggested, what you will really get is:

03-JUN-2008 00:00:00

Why would this be a problem? Let's say you use that date to test against end-of-day, such as counting all user comments submitted up to, and including, 06/03/2008. If you compare against the date above, you will end up excluding all comments made on that date since they will all be made at 00:00 plus hours.

So in that case you will need to add the time to make it truly end of day, for example:

to_date('03JUN2008','DDMONYYYY')+0.999988425925926

which will give you:

03-JUN-2008 23:59:59