Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Wed, 22 Aug 2012 @ 19:16:39 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: How to replace with null if date is invalid
 
From:   Kenneth Hansen

Consider the proportion of the table on which you want to change the date. If significant then do not update but insert the correct values into a new table and then drop the old table after testing.

I apologise for previously overlooking the impact of existing NULLS on processing "BETWEEN". Use a Case statement and start by identifying the NULLS so that subsequent logic will work. Then the largest proportion next, the lowest proportion last.

     Case < columnname >
     when is null then null else
     when < date '1950-01-01' then NULL else
     when > (Current_Date plus interval 5 year then NULL else
     < columnname > end

Being cautious - I would double check you have a copy saved before you test your code.


Good luck

Ken



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020