0

I have one datetime field(startdatetime) store in Netezza DB as varchar because SSIS is truncating milisec and I want datetime till milisec.

So now I have startdatetime value is like 2014-1-2 11:23:31.921000000 and I want it 2014-01-02 11:23:31.921 Length should be 23. How can I do that?

So what I am doing is I want to do incremental load so i am getting max(startdatetime) from the table and compare it to startdatetime (datetime data type) field coming from source so when startdatetime > max(startdatetime), load that new data. So for that purpose I need to convert it to like 2014-01-02 11:23:31.921

Thanks for the help.

user3498241
  • 11
  • 1
  • 1
  • It's unclear to me what you're asking for here. Is startdatetime stored in Netezza as a VARCHAR in all references to columns of that name, or are some timestamps? Are you asking how to compare a VARCHAR representation of a timestanp to an actual timestamp? – ScottMcG Aug 17 '14 at 16:40
  • Perhaps some sample DDL would help – ScottMcG Aug 17 '14 at 16:55

1 Answers1

1

I think what you're asking for is the to_char function, specifically to_char(startdatetime, 'YYYY-MM-DD HH-MI-SS.US'). See IBM's documentation.

If that still leaves too many '0' characters on the end, try using the regex functions to replace it.

regexp_replace(to_char(startdatetime, 'YYYY-MM-DD HH-MI-SS.US'), '0+$', '')

Jeremy Fortune
  • 2,459
  • 1
  • 18
  • 21