Having an issue with timestamps from my oracle server being in different format on different clients despite

0
votes

Having an issue with vb.net clients interpreting timestamps from my oracle 11g server differently. I am using VS2010, VB.NET, 11.2.0.30 driver, Win XP on the client machines.

the software is identical on both machines, and the users have the same permissions on the server.

When I login through toad with each users credentials and do a SELECT SYSDATE FROM DUAL; I get YYYY-MM-DD HH24:MI:SS format.

through my application when i do a SELECT SYSDATE FROM DUAL; PC1: DD/MM/YYYY HH24:MI:SS PC2: YYYY-MM-DD HH24:MI:SS

I did change my serverside settings to use the YYYY-MM-DD 24HH:MI:SS format Why is the setting being overruled on only some of the pcs? and how can i get it consistent for all clients?

thanks in advance.

1
vb.netoracledatetimetimestampdriver

1 Answers

1
votes

Each client has its own NLS_DATE_FORMAT setting, which might be set somewhere visible (e.g. in Toad), or might be defaulted by the platform or driver, and might be inherited indirectly through locale settings. Having NLS_TIMESTAMP_FORMAT overridden by default is less common I think, but I'm not sure if you're referring to Oracle TIMESTAMP or DATE, which also has a time component.

The client setting always overrides the server setting, so changing the server is unlikely to help, and you might just have some clients that are already set with a matching format. The client setting can itself be explicitly overridden after you connect at session level with an alter session command, or individually in SQL statements. The precedence is shown in the globalization support guide.

In general you should never rely on implicit data conversions. Other than in simple ad hoc queries, date are normally selected for display using an explicit date format mask, e.g. select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual. If you have an application that expects a date to be returned (as a string) in a particular format then it's safer to do that... though normally you'd pull a DATE back and let the client do the formatting anyway.

It's arguably even more important to always specify a format mask when inserting data from a string with a to_date() call, but again your client would normally handle that and pass a DATE.