Hi there,
we are using an insert statement like the following :
INSERT INTO XYZ_c
(abc_date, 123_date, abc_abc_12,
cde_cde_cde, abe_code, db_channel,
efg_hij_date, jjg_date, abc_abc_code,
opq_amount, abc_opq_amount,
wxy_ixz_seq, jhk_id, klm_id, le_ste_id,
hg_sic_von, pct_code_type, db_ue_tp
)
VALUES (TO_DATE (:1, 'DD-MON-YY'), TO_DATE (:2, 'DD-MON-YY'), :3,
:4, :5, :6,
TO_DATE (:7, 'DD-MON-YY'), TO_DATE (:8, 'DD-MON-YY'), :9,
:10, :11,
:12, :13, :14, :15,
:16, :17, TO_DATE (:18, 'YYYY-MM-DD HH24:MI:SS')
)
since it's unlikely to cause a problem imminently, but generally speaking it's better to always use 4-digit years, not 2 as is used here.
Oracle has to make an assumption about the century unless you tell it explicitly what it is, and whilst it might work now, there's no guarantee that future versions of Oracle will make the same assumption. You should also avoid using character month abbreviations - (ie MON) - use numeric months instead (MM). This is because the charachter abbreviations are determined by the client NLS settings (ie how the DataStage job connects to db), and different languages use different abbreviations, but numeric representation of months is pretty standardised.
can anyone suggest how can i implement the above thing.
thanks
NLS SETTINGS?
Moderators: chulett, rschirm, roy
NLS SETTINGS?
Last edited by karry450 on Mon Nov 19, 2007 12:18 am, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Let's say you're processing dates in a Transformer stage. Use the following commands in ExecTCL as before-stage and after-stage subroutines.
Before-stage: SET.LOCALE TIME FR-FRENCH
After-stage: RESTORE.LOCALE
This will mean that, irrespective of what the rest of the job is doing, the Transformer stage will operate in French as far as dates and times are concerned; it will correctly recognize French day names and month names and their abbreviations.
Before-stage: SET.LOCALE TIME FR-FRENCH
After-stage: RESTORE.LOCALE
This will mean that, irrespective of what the rest of the job is doing, the Transformer stage will operate in French as far as dates and times are concerned; it will correctly recognize French day names and month names and their abbreviations.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: NLS SETTINGS?
You could use the oracle date format RR instead of YY
as in TO_DATE(date,'DD-MM-RR')
I believe it was set up to cope with the transiton between centuries.
as in TO_DATE(date,'DD-MM-RR')
I believe it was set up to cope with the transiton between centuries.