Strange format on timestamp first hour after midnight

[Former Member]

SELECT lastModifiedDate,DateFormat(lastModifiedDate,'dd.mm.yyyy hh.nn.ss.ssssss') as dtFormatted FROM StatData WHERE DateFormat(lastModifiedDate,'hh') = '00'

Gives the result:
2010-10-25 00:0-43:03.592000 25.10.2010 00.17.03.592000
2010-10-21 00:0-30:45.950000 21.10.2010 00.30.45.950000
2010-10-13 00:0-40:34.216000 13.10.2010 00.20.34.216000
2010-10-21 00:0-58:41.947000 21.10.2010 00.02.41.947000
2010-10-25 00:0-43:03.592000 25.10.2010 00.17.03.592000

What's with that?

mvh Ove B-)

[MCMartin]

Please tell us the SQLA version you use

[MarkCulp]

Yes, please tell us the version and build number that you are using. I just tried v16 and it seems to work as expected:

select cast( '2010-10-25 00:43:03.592000' as timestamp ) d, DateFormat(d,'dd.mm.yyyy hh.nn.ss.ssssss')

gives

2010-10-25 00:43:03.592   25.10.2010 00.43.03.592000

[Breck_Carter]

That looks like a client or interface issue, since it is the first lastModifiedDate column that is corrupted (presumably a TIMESTAMP).

The DATEFORMAT column (which is a string) looks OK which implies the server is working OK.

So... what client software are you using? dbisql?

What interface? ODBC?

[Former Member]

Ah, I should have guessed, or at least tried isql...

We are using a rather old jconn4.jar, dated 27.01.2010, running against a dbsrv12 GA 12 0 1 3967 linux 2013/09/04 15:54:03 posix 64 production. Will try a newer driver and let you know.

Ove B-)