Have Geek Brain

Import date format mmddyyyy

I need to import dates in oracle with this format type

03JUN2008

The only example I've seen before has a dash, and it doesn't.

Should I just cast it as a varchar and manipulate or is there another way?

thanks

+1


a source to share


3 answers


If it's a date then store it as a date, don't get confused with storing it as varchar or any such nonsense.

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

If you are just using SQL statements 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@:>
+3


a source


I don't know if I understand what you mean, but I think you can do it simply with TO_DATE, examples and details here.



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

+2


a source


To get a good answer, you need 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, you actually get:

03-JUN-2008 00:00:00

Why would this be a problem? Let's say you use this date for testing against the end of the day, for example, counting all user comments posted before and including 06/03/2008. If you compare against the above date, you will eventually exclude all comments made on that date, as they will all be made at 00:00 plus hours.

So, in this case, you will need to add the time to make it a truly complete day, for example:

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

which will give you:

03-JUN-2008 23:59:59
0


a source






More articles: