invalid input syntax for type interval: "1 microsecond"

Question

This works:  
   to_date('2020-11-02','YYYY-MM-DD') + interval '1 days' => 2020-11-03 00:00:00.000000  
  
And this works:  
   DATEADD( 'microsecond', 1, to_date('2020-11-02','YYYY-MM-DD') ) => 2020-11-02 00:00:00.000001  
  
But this doesn't:  
   to_date('2020-11-02','YYYY-MM-DD') - interval '1 microsecond'   
=> \[Amazon](500310) Invalid operation: invalid input syntax for type interval: "1 microsecond"; \[SQL State=22007, DB Errorcode=500310]  
  
Seems a bit inconsistent? Is this expected behaviour?  
  
Edited by: BiasedEU on Nov 3, 2020 2:53 AM

AWS-User-2965899 · Answer

Microsecond is not currently supported for INTERVAL operations. DATEADD is the work around as you've noted.  
  
We've made a note of this and will consider adding to our roadmap. When new features are released they are noted in our regular maintenance announcements at the top of the forum.

invalid input syntax for type interval: "1 microsecond"

Add your answer

Relevant content