CODEWITHSUNDEEP
sqlssisdatetime-formatnetezza
user3498241
user3498241

Reputation: 11

How to change date format in Netezza?

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.

Upvotes: 0

Views: 4129

Answers (1)

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

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+$', '')

Upvotes: 1

Related Questions