0

I have a requirement where I have to insert date fields into a varchar column. I am directly inserting the record into the table without any typecast. In one environment it's inserting as 29-AUG-16, and in the other environment it's storing as 29-08-2016. Hence, my subsequent processing of the data is getting failed. I am expecting data as DD-MON-RR in both environment. In both environment nls format is DD-MON-RR in the nls session parameters table. What is the reason for the difference in two environment? I tried typecast using TO_CHAR(DATECOL, 'DD-MON-RR' ) But still data is inserted as dd-mm-yyyy format

Sid
  • 582
  • 3
  • 7
  • 28
  • 3
    "a requirement to insert date fields into a varchar column". Just wondering, why? – Pavel Smirnov Apr 03 '19 at 14:50
  • Because we create a primary key using date and concatenate with other key fields. – Sid Apr 03 '19 at 14:58
  • You're creating an extra column with concatenated values from other columns - and making that the primary key; rather than having a compound primary key across the original columns? Seems a bit odd... Anyway, you shouldn't rely on NLS settings, and how you actually perform the insert might be ignoring them anyway - where you are getting the date value from, for instance (it may be a string earlier than you think?), and what binding or explicit/implicit conversion is happening. – Alex Poole Apr 03 '19 at 15:26
  • Please include your full insert statement, along with the DDL for the table(s), or at least the actual data types of the columns and any variables you are using. – Alex Poole Apr 03 '19 at 16:40

2 Answers2

2

You can use TO_CHAR function to convert date into character and insert it into varchar column.

Instead of simply writing date_column , you should use something like this in your insert query

INSERT INTO date_tab VALUES(TO_CHAR(date_col, 'DD-MON-YY'))

Jinesh Shah
  • 922
  • 10
  • 18
0

NLS values are set per session depending on server defaults, client defaults and explicit session settings. So if you want a standard format for all sessions you have to set desired NLS at operation level to override all possible (almost unpredictible) NLS resulting combinations. In to_char function you should set the third parameter (NLS) to your selected NLS date format.

Sal
  • 1,307
  • 1
  • 8
  • 16
  • I tried inserting using TO_CHAR(DATECOL, 'DD-MON-RR'). However, still value is inserted as mm-dd-yyyy format for unknown reason – Sid Apr 03 '19 at 16:07
  • Try third parameter `to_char(datecol, 'DD-MON-RR', 'NLS_DATE_LANGUAGE=english')`. On another side, I wonder if there is a trigger involved. – Sal Apr 03 '19 at 16:16
  • Thanks. But it didn't help and there is no trigger involved. The query I working fine in Dev but not in higher environment – Sid Apr 03 '19 at 16:23
  • It is close to impossible for a value to be inserted as mm-dd-yyyy if you use an explicit TO_CHAR(datecol, 'DD-MON-RR'). You should double-check your code. In particular, make sure not to use TO_DATE mistakenly or use a wrong column type at source or target. – Sergiusz Wolicki Apr 11 '19 at 02:15