Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: Odbc and date format

    Im trying to change the default format of date when I do a select * from myTable. The date look like : 1999-07-06 14:10:54.000000 Obviously I dont whant the .000 part. I tryed to alter the session NLS_DATE_FORMAT parameter but it didnt changed it ;-(

    Im using Oracle 10g xe odbc driver on windoze.

    Thx

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "It didn't change it" is not very descriptive. Why not? How did you try to alter session? Did you get any ORA- message? If yes, which one?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You mention ODBC. Which tool do you use to retrieve the data?
    The NLS_DATE_FORMATE is (as far as I know) only used by SQL*Plus to format output (which does not use ODBC). Other tools might have other settings.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    You mention ODBC. Which tool do you use to retrieve the data?
    The NLS_DATE_FORMATE is (as far as I know) only used by SQL*Plus to format output (which does not use ODBC). Other tools might have other settings.
    Code:
    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
    definitely works for any Oracle session, it is not a SQL*Plus command .

    The problem is that here, artois_val, you are getting a TIMESTAMP field, so the right parameter to change is NLS_TIMESTAMP_FORMAT :
    Code:
    rbaraer@Ora10g> select sysdate from dual;
    
    SYSDATE
    ---------
    13-NOV-06
    
    rbaraer@Ora10g> select to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;
    
    TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'),'YYYY-MM-DDHH24:MI:SS'
    ---------------------------------------------------------------------------
    13-NOV-06 07.37.55.000000000 AM
    
    rbaraer@Ora10g> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
    
    Session altered.
    
    rbaraer@Ora10g> select sysdate from dual;
    
    SYSDATE
    -------------------
    2006-11-13 07:38:10
    
    rbaraer@Ora10g> select to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;
    
    TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'),'YYYY-MM-DDHH24:MI:SS'
    ---------------------------------------------------------------------------
    13-NOV-06 07.38.13.000000000 AM
    
    rbaraer@Ora10g> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF3';
    
    Session altered.
    
    rbaraer@Ora10g> select to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;
    
    TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'),'YYYY-MM-DDHH24:MI:SS'
    ---------------------------------------------------------------------------
    2006-11-13 07:38:25.000
    
    rbaraer@Ora10g> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    13-NOV-06 07.38.40.847340 AM +01:00
    
    rbaraer@Ora10g>
    You will notice that it has no impact on systimestamp, though...
    Code:
    rbaraer@Ora10g>  alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF3';
    
    Session altered.
    
    rbaraer@Ora10g> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    2006-11-13 07:43:10.414
    
    rbaraer@Ora10g>
    This is because SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE, which display format is derived from NLS_TIMESTAMP_TZ_FORMAT .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by RBARAER
    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI: SS';
    definitely works for any Oracle session, it is not a SQL*Plus command
    Of course it does, I never denied that. As a matter of fact it does impact the way date literals are processed when sent from the client to the database.

    But we are talking about output formatting here, which is the responsibility of the client application (that visualizes the data).

    SQL*Plus is just another client application and it happens to use NLS_DATE_FORMAT also for formatting the output. But that does not mean that every client tool that accesses an Oracle database does that.
    So if the OP is accessing Oracle using ODBC it's pretty clear, SQL*Plus is not involved and thus we need to know which application she/he is using.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    Of course it does, I never denied that.
    Good.
    Quote Originally Posted by shammat
    As a matter of fact it does impact the way date literals are processed when sent from the client to the database.
    Yes it does, but we are talking only about one way here : from the db to the client since the query is a SELECT.
    Quote Originally Posted by shammat
    SQL*Plus is just another client application and it happens to use NLS_DATE_FORMAT also for formatting the output. But that does not mean that every client tool that accesses an Oracle database does that.
    So if the OP is accessing Oracle using ODBC it's pretty clear, SQL*Plus is not involved and thus we need to know which application she/he is using.
    I used nothing specific to SQL*Plus. That was my point. ALTER SESSION works for ANY Oracle session, be it an ODBC connection, JDBC connection, OCI connection... all the same. I personally use ALTER SESSION NLS_DATE_FORMAT with SQL Developer since there is no parameter to tell which format we want for DATE display. One could use ALTER SESSION in a program after each connection so as to set some NLS parameters...

    Now there may be some other solutions depending on the tool the OP is using, on this I agree, but I wanted to point out that the OP was misusing ALTER SESSION for TIMESTAMP display, and that if it were used correctly, the "SELECT *" query could be formatted as wanted. NLS_DATE_FORMAT only works for DATE while NLS_TIMESTAMP_FORMAT should be used for timestamps and NLS_TIMESTAMP_TZ_FORMAT for timestamps with time zone.

    I hope we agree.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    ALTER SESSION works for ANY Oracle session, be it an ODBC connection, JDBC connection, OCI connection...
    Of course ALTER SESSION works for any client interface, because this is executed on the server and changes a parameter that is stored on the server (for that specific session)
    But nothing forces the client application to read and respect this session setting when retrieving and displaying data on the client (be it dates, timestamps or numbers).

    I used nothing specific to SQL*Plus
    In a way you did. Because SQL*Plus evaluates the value of that session parameter when displaying data.

    SQL*Plus ad SQL Developer happen to be both from Oracle and Oracle is trying to make SQL Developer as compatible to SQL*Plus as possible. Actually I would very surprised if SQL Developer had not respected the NLS_xxx parameters when formatting the output

    I use my own (JDBC based) tool and this does not respect or use any of the NLS_xxx parameters. I would assume no cross-DBMS tool will make use of these parameters as they are specific to Oracle (haven't checked Squirrel, DbVisualizer or DatabaseWorkbench though). Using the DBMS specific stuff would require quite some effort compared to using a user-defined format (independently of the DBMS)

    and that if it were used correctly, the "SELECT *" query could be formatted as wanted
    Not unless we can be sure that the OP's client application does read the session parameter and actually uses it. That's all I'm saying. I do have the feeling you are saying the same, but this is somewhat shaded by the fact that you are mixing "responsibilities" somehow

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    Of course ALTER SESSION works for any client interface, because this is executed on the server and changes a parameter that is stored on the server (for that specific session)
    But nothing forces the client application to read and respect this session setting when retrieving and displaying data on the client (be it dates, timestamps or numbers).

    In a way you did. Because SQL*Plus evaluates the value of that session parameter when displaying data.

    SQL*Plus ad SQL Developer happen to be both from Oracle and Oracle is trying to make SQL Developer as compatible to SQL*Plus as possible. Actually I would very surprised if SQL Developer had not respected the NLS_xxx parameters when formatting the output

    I use my own (JDBC based) tool and this does not respect or use any of the NLS_xxx parameters. I would assume no cross-DBMS tool will make use of these parameters as they are specific to Oracle (haven't checked Squirrel, DbVisualizer or DatabaseWorkbench though). Using the DBMS specific stuff would require quite some effort compared to using a user-defined format (independently of the DBMS)

    Not unless we can be sure that the OP's client application does read the session parameter and actually uses it. That's all I'm saying. I do have the feeling you are saying the same, but this is somewhat shaded by the fact that you are mixing "responsibilities" somehow
    What you say is bothering me...

    So we agree that ALTER SESSION works and updates the session parameter on the server, BUT you say that the client application may nevertheless ignore NLS features since it would not have implemented their use.

    I understand there are some applications that do not use OCI on the client, such as JDBC-THIN, so potentially you may be right. However, I would have thought that the server process managing the session would have taken care of NLS settings and performed the necessary conversions before sending data to the client application. If it would not and if the client application, as you say, does not implement these features, then how could data be interpreted correctly by the client ? Date display is one thing, but what of character set conversions ? In your hypothesis, would the client app always receive data in the database charset whatever the specified client charset ?

    Would I be too Oracle-biased ?

    I really would like to have clear ideas about this.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    So we agree that ALTER SESSION works and updates the session parameter on the server
    Absolutely, and for things like date literals that are parsed by the server (UPDATE theTable SET dateCol = '2006-11-14', or SELECT to_date(varchar_column) FROM theTable) the session settings do apply.
    However, I would have thought that the server process managing the session would have taken care of NLS settings and performed the necessary conversions before sending data to the client application. If it would not and if the client application, as you say, does not implement these features, then how could data be interpreted correctly by the client ? Date display is one thing, but what of character set conversions ? In your hypothesis, would the client app always receive data in the database charset whatever the specified client charset ?
    As far as I know the server sends "binary" data in some form. It is up to the client (driver) to "decode" this into sensible data for the application calling the driver's function. So for date (and timestamp) columns I'm nearly 100% sure that a numeric values will be sent over the wire.

    I think the behaviour for character encodings is the same. The reason why I believe this, is that for older JDBC drivers you always needed an additional library that would implement the character encoding.

    The problem with verifying this (apart from asking someone at Oracle) is that when it comes to displaying characters the "display" API also comes into play, and it's hard to tell where the encoding is lost (I have seen older TOAD versions not beeing able to display UTF8 characters correctly whereas my Java program could)

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    Absolutely, and for things like date literals that are parsed by the server (UPDATE theTable SET dateCol = '2006-11-14', or SELECT to_date(varchar_column) FROM theTable) the session settings do apply.
    As far as I know the server sends "binary" data in some form. It is up to the client (driver) to "decode" this into sensible data for the application calling the driver's function. So for date (and timestamp) columns I'm nearly 100% sure that a numeric values will be sent over the wire.

    I think the behaviour for character encodings is the same. The reason why I believe this, is that for older JDBC drivers you always needed an additional library that would implement the character encoding.

    The problem with verifying this (apart from asking someone at Oracle) is that when it comes to displaying characters the "display" API also comes into play, and it's hard to tell where the encoding is lost (I have seen older TOAD versions not beeing able to display UTF8 characters correctly whereas my Java program could)
    Thanks for these precisions, it's good to be aware of such potential issues !

    Now back to the OP's problem (artois_val where are you ? ) I would be extremely surprised if NLS settings would not work since we're talking about ODBC here. We have tried desperately to make Oracle ODBC connections work without an Oracle client (in order to use stored procedures with Crystal Reports) but could not. Be it Oracle's driver or Microsoft's driver, they both need OCI8. This schema explains why (this is the Oracle driver's doc but it must be almost the same for Microsoft's one since it also complains when the Oracle client is not installed...).

    When the OP comes back, maybe we will have further information of what exactly is going wrong...

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  11. #11
    Join Date
    Mar 2004
    Posts
    12
    Quote Originally Posted by RBARAER
    Thanks for these precisions, it's good to be aware of such potential issues !

    Now back to the OP's problem (artois_val where are you ? ) I would be extremely surprised if NLS settings would not work since we're talking about ODBC here. We have tried desperately to make Oracle ODBC connections work without an Oracle client (in order to use stored procedures with Crystal Reports) but could not. Be it Oracle's driver or Microsoft's driver, they both need OCI8. This schema explains why (this is the Oracle driver's doc but it must be almost the same for Microsoft's one since it also complains when the Oracle client is not installed...).

    When the OP comes back, maybe we will have further information of what exactly is going wrong...

    Regards,

    rbaraer

    Thanks alot for your help ;-)

    I did try to change the nls_timestamp_format param instead of nls_date_format. With Odbc it didnt change anything, same value with or without the alter session. However when I use the oci8, it affect the result.

    The odbc application is a classic win32 c++ one (also I tested it with c# with OdbcConnection). The oci8 app is a php page, we use different plate-form at work.

    I may do something wrong too but if it the case I clueless why ;-)

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by artois_val
    Thanks alot for your help ;-)

    I did try to change the nls_timestamp_format param instead of nls_date_format. With Odbc it didnt change anything, same value with or without the alter session. However when I use the oci8, it affect the result.

    The odbc application is a classic win32 c++ one (also I tested it with c# with OdbcConnection). The oci8 app is a php page, we use different plate-form at work.

    I may do something wrong too but if it the case I clueless why ;-)
    You're welcome. I'm sorry this does not work. What is the Oracle datatype of the column ?

    What ODBC driver are you using ? You do need an Oracle client installed for it to work, don't you ?

    Edit : a solution that would always work and that I would encourage instead of relying on NLS parameters, even if you were using OCI, would be to always convert explicitly dates to strings with TO_CHAR and strings to dates with TO_DATE or TO_TIMESTAMP depending on the datatype of the date column. Put the format you want to use for dates and the one you want to use for timestamps in a package as package variables, create functions that return them and then always call these functions as formats in TO_CHAR, TO_DATE and TO_TIMESTAMP, so that you're sure you make no mistake. Then transform the strings as you want to display them on the client. As shammat suggested, there are two levels : the one where you exchange data with the database and the one where you display data to /get data from the end user.

    For example :
    Code:
    rbaraer@Ora10g> CREATE OR REPLACE PACKAGE PKG_DateFormats AS
      2
      3      DateFormat VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS';
      4      TimestampFormat VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS.FF6';
      5
      6      FUNCTION getDateFormat RETURN VARCHAR2;
      7
      8      FUNCTION getTimestampFormat RETURN VARCHAR2;
      9
     10  END PKG_DateFormats;
     11  /
    
    Package created.
    
    rbaraer@Ora10g> CREATE OR REPLACE PACKAGE BODY PKG_DateFormats AS
      2
      3      FUNCTION getDateFormat RETURN VARCHAR2 IS
      4
      5      BEGIN
      6
      7          RETURN DateFormat;
      8
      9      END getDateFormat;
     10
     11      FUNCTION getTimestampFormat RETURN VARCHAR2 IS
     12
     13      BEGIN
     14
     15          RETURN TimestampFormat;
     16
     17      END getTimestampFormat;
     18
     19  END PKG_DateFormats;
     20  /
    
    Package body created.
    
    rbaraer@Ora10g> SELECT TO_CHAR(sysdate, PKG_DateFormats.getDateFormat()) TodaysDate, TO_CHAR(systimestamp, PKG_DateFormats.getTimestampFormat()) TodaysTimestamp FROM DUAL;
    
    TODAYSDATE
    ---------------------------------------------------------------------------
    TODAYSTIMESTAMP
    ---------------------------------------------------------------------------
    2006/11/16 07:09:58
    2006/11/16 07:09:58.307462
    
    
    rbaraer@Ora10g>
    BTW, "SELECT *" is a bad habit since your program could get lost when you drop/add a column.

    HTH & Regards,

    rbaraer
    Last edited by RBARAER; 11-16-06 at 02:14.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  13. #13
    Join Date
    Mar 2004
    Posts
    12

    Unhappy

    Hehe I agree 100% with you, but where I work, they use ms acces with odbc since ten years, and they just accepeted to try Oracle on a pet project, to test it for reliability ;-) My job is to help them migrate to Oracle 10g xe.

    The version is 10g express. And I can't make all of the other developers change all their bad habits in a finger click, even if I wished. But thx again for your help. I will investigate further into the timestamp.

  14. #14
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by artois_val
    Hehe I agree 100% with you, but where I work, they use ms acces with odbc since ten years, and they just accepeted to try Oracle on a pet project, to test it for reliability ;-) My job is to help them migrate to Oracle 10g xe.

    The version is 10g express. And I can't make all of the other developers change all their bad habits in a finger click, even if I wished. But thx again for your help. I will investigate further into the timestamp.
    I understand. Good luck then !

    BTW you didn't answer my question about which ODBC driver you were using and if you didn't need to install Oracle client .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  15. #15
    Join Date
    Mar 2004
    Posts
    12
    Quote Originally Posted by RBARAER
    I understand. Good luck then !

    BTW you didn't answer my question about which ODBC driver you were using and if you didn't need to install Oracle client .

    Regards,

    rbaraer
    Oh sorry, the one that is included with oracle express.

    I think it 10.1.0.2.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •