0

I want to load data with date format as 'MM/DD/YYYY', but currently facing a challenge with below data in input file.

e.g. As per below code it's allowing 09/01/20 and 09/11/2020. but i want to reject the 09/01/20 as YYYY is not 4 digit.

How can I reject this data?

Code:

load data
infile 'file1.csv'
append
into table my_table
fields terminated by ',' TRAILING NULLCOLS
(Business_function, 
Case_reference, 
Sub_sequence, 
Dialler_Master_Stream ,
Dialler_Call_Stream,    
Dialler_Super_Stream, 
Attempt_Number, 
Dialled_Number, 
Date_Called DATE "MM/DD/YYYY")
halfer
  • 19,824
  • 17
  • 99
  • 186
Ganesh
  • 486
  • 3
  • 8
  • 18

1 Answers1

0

Are you sure you don't want the date format to be "MM/DD/RRRR" Which will handle the century part for you and make it 4 digits?

See The RR Datetime Format Element for more detailed information.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Actually i need to error out this if i get any unformatted data as part of data validation steps. like '11/23/17' . it should be MM/DD/YYYY format only – Ganesh Nov 23 '20 at 04:11