Home / Questions / 465282
Accepted
EduBw
Asked: 2022-06-29 06:53:17 +0800 CST 2022-06-29 06:53:17 +0800 CST

I lose the hours in Select in Oracle

  • 772

I am using PL/SQL to query and do logic on the database.

For this build I need to get the oldest date from the table.

If I do a select to the table I get: (for example) Without PL/SQL from the sqlWindows option

21/10/2012 10:10:20

That is, it outputs dd/MM/yyyy hh:mm:ss

Later when I do my PL/SQL code to perform the operations:

     fecha VARCHAR2(100) := null;
     -- fecha DATE := null
     -- INTENTADO DE LAS 2 MANERAS.

     BEGIN
      SELECT FEC_ALTA
      INTO fecha
      FROM articulo
      WHERE cod_art = '1a';
          
          ...
      EXCEPTION

The thing is, it doesn't matter if you use the into date as a varchar2 or as a date, because it loses the hh:mm:ss. In debugging time when inspecting the variable it doesn't have the hh:mm:ss and when the result is returned to JAVA it doesn't have hh:mm:ss either...

It is Oracle and the IDE PL/SQL DEVELOPER 11.0.6 is being used Thank you.

sql

2 Answers

  1. The first thing is that you should NEVER manipulate a data of type DATE through VARCHAR2 variables.

    Oracle always stores a value of type DATE in 7 bytes:
    century
    year
    month
    day
    hours (0 to 23 hours)
    minutes (0 to 59)
    seconds (0 to 59)

    This is totally independent of country, language, time zone.

    What you see on the screen is an automatic conversion carried out by your IDE tool (for example SQL-Developer or with SQL-Plus) according to the default language-language configuration, or to the fact that you explicitly applied the command

    ALTER SESSION SET NLS_DATE_FORMAT

    If for the ARTICLE table the HIGH_FEC column is of type DATE then simply use the following to get the smallest date:

    Declare
  fecha date;
BEGIN
  SELECT MIN(FEC_ALTA)
  INTO fecha
  FROM articulo
  WHERE cod_art = '1a';
  --
  -- si lo va a "visualizar" Y no usa TO_CHAR entonces Oracle le aplica el formato default 
  -- según la configuración de idioma-región que tenga el IDE
  Dbms_Output.Put_Line('fecha='||fecha));
  --
  -- Si usa la función TO_CHAR usar el parámetro 2 con el formato que desee
  -- por ejemplo el formato ISO-8601 para evitar confusiones
  Dbms_Output.Put_Line('fecha='||to_char(fecha,'yyyy-mm-dd hh24:mi:ss'));
End;

    I don't know Java, so I don't know if that language has any data type equivalent to Oracle's DATE.

    If definitely for Java you have to deliver a string then only in the output variable to Java use the TO_CHAR function explicitly with the format ISO-8601 yyyy-mm-dd hh24:mi:ss to convert a value of type DATE to a value VARCHAR2 type, which guarantees that the strings have the sequence of year-month-day-hours-minutes-seconds and that if you order them they are ordered naturally.

    • 1
  2. Best Answer

    We must assume that your field is datetime or timestamp. Since you don't put the definition of the table in the question, we will have to guess.

    To get the data in the specific format you want, you can use to_charto get a string expressing the date as the documentation says . By doing the select without more, you are really applying a default mask that only brings the date.

    Example:

    SELECT TO_CHAR(FEC_ALTA, 'DD-MON-YYYY HH24:MI:SSxFF') 
INTO fecha
FROM articulo
WHERE cod_art = '1a';

    And it will give you an output similar to21-OCT-2012 10:10:20

    • 0