CODEWITHSUNDEEP
netezza
Will
Will

Reputation: 8631

Netezza Timesptamp failing

I'm trying to query the database as flows:

select count(distinct(TE_ID)) from TE where LAST_UPDATE_TIME >= '2013-01-08-00:00:00.000000' and LAST_UPDATE_TIME < '2013-01-09-00:00:00.000000'

However the error I receive is:

 11:25:09  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 1100, SQL State: HY000]  ERROR:  Bad timestamp external representation '2013-01-08-00:00:00.000000'

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Upvotes: 1

Views: 6595

Answers (2)

Niederee
Niederee

Reputation: 4295

The timestamp you are giving has an extra dash.

Yours: select cast('2013-01-08-00:00:00.000000' as timestamp)

Should be: select cast('2013-01-08 00:00:00.000000' as timestamp)

To control it might be a good idea to explicitly cast like the example below:

to_timestamp('2013-01-08 00:00:00.000000','YYYY-MM-DD HH:MI:SS.US')

  • HH = Hour
  • MI = Minute
  • SS = Second
  • US = Microseconds

Upvotes: 1

E.T.
E.T.

Reputation: 944

Try this:

  select count(distinct(TE_ID)) from TE where LAST_UPDATE_TIME >= '2013-01-08 00:00:00.000000' and LAST_UPDATE_TIME < '2013-01-09 00:00:00.000000'

Upvotes: 0

Related Questions