Forum Stats

  • 3,770,135 Users
  • 2,253,074 Discussions
  • 7,875,339 Comments

Discussions

Alter session statement in View

User_JZKI6
User_JZKI6 Member Posts: 38 Green Ribbon

Hi Everyone,

Actually we created view for calculating the business Hrs and Minutes between 2 dates from the main table. View will show the data in date format, after alter session we could see in Hrs and Min format.

For few of the records there was difference in Hrs and Minutes count before and after altering the session. How Can we include the below alter statement when we crating the view for showing unique values.

alter session set nls_date_format='YYYY-MM-DD HH24:MI';

when we are trying to create view including alter facing issues.

Oracle Sql version we are using :  Version 20.2.0.175


Thanks,     

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond

    Alter session is a statement that sets the parameters for the current session (not all users, just the one connected to at the time you issue it). A view is a stored SQL query i.e. a select statement. There is no way to embed the alter session statement in to the view.

    If you need to show dates in a particular format as part of your view, then you would need to enforce the format using TO_CHAR around the date value and that will turn it to a string in the format of your choosing. However that does have drawbacks, especially if the person viewing the data needs to sort the data based on the dates for example. In your case you may be lucky because you're formatting it from largest granularity to smallest anyway (year first, then month, then day, then hours, then minutes). If the format were any different to that then sorting would be all wrong.

    Ideally, the client that the user is using to view the data, should present it in the format that is local to themselves. If they need to see times as well, then the client configuration should be set up to show times.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_JZKI6

    For few of the records there was difference in Hrs and Minutes count before and after altering the session. 

    It sounds like you have a problem using the view, not creating it.

    Whenever you have a problem, post some code that the people who want to help you can run to re-create the problem on their own systems and test their ideas. In this case, include a CREATE VIEW statement, CREATE TABLE and INSERT statements for any tables needed, ALTER SESSION statements and whatever code is not behaving the way you want. Point out where you're getting the wrong results, show what the correct results would be, and explain how you get them.

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond

    Does your view return a date column as a date, or converted to a varchar2? I suspect the former. Run this in SQL*Plus to verify:

    desc v_your_view_name;

    If it's a date type, then your problem is just a client issue and you need to set your session date format, or use to_char to format how you want to see it. Many clients don't show the hours, mins and seconds if the date's time is midnight, which might be your problem.

  • jflack
    jflack Member Posts: 1,516 Bronze Trophy
    edited Jun 25, 2021 5:16PM

    I would like to see the SELECT behind your view, and name the column that you are having trouble with. When you are trying to see time between two DATEs subtracting one date from the other gets you days and fractions of a day. But you could also convert to an INTERVAL, and there are ways to format an INTERVAL.

    But remember, formatting a column in a query is a CLIENT-side function, unless you use a TO_CHAR function, which has problems that have already been mentioned. The NLS formats simply suggest to the database what implied conversions should do - converting to a DATE without an explicit TO_DATE or to a human readable format without an explicit TO_CHAR.