in oracle db format of date 04-DEC-20 while displaying in console shows date like 04-09-2020

Question

i need to fetch last modified date of item in table(oracle db). in db format of modified date=04-DEC-20 while displaying in console modified date=04-09-2020

c# code to fetch modified date

             string connString =DBUtils.GetDBConnection();
      
                        
            OracleConnection conn = new OracleConnection();

            conn.ConnectionString = connString;

            string sql = "select LASTMODIFIED , name from v_vname where name in('hector')";
            OracleCommand cmd = new OracleCommand();


           
                // Set connection for command.
                cmd.Connection = conn;
                cmd.CommandText = sql;
                conn.Open();
        using (DbDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {

                while (reader.Read())
                {
                    
                    int date = reader.GetOrdinal("LASTMODIFIED"); // 0

                    var LASTmTime = reader.GetValue(0);
                 Console.WriteLine("modifieddate:" + LASTmTime);
                  }
              }
         }

You might want to check two things: TO_CHAR function and it's format masks and/or NLS settings. However, the main thing would be - if you have date/timestamp in DB, better to read those as temporal in the app and then format those accordingly only when you are outputting those — micklesh, Dec 19 '20 at 10:28
Both c# and Oracle use the same DateTime format (a number) to store the date. When debugging in c# you see the default method VS uses to display the date. When displaying a ToString() default method is used, So the dates are exactly the same in Oracle and c# they are just being displayed in a different format. — jdweng, Dec 19 '20 at 10:42
What is the data type of LASTMODIFIED column? If it is DATE, then your assertion that its format is is incorrect. DATE datatypes have a consistent, internal, binary formatting. What you _see_ and _report_ as is merely a character representation of the date, resulting from implicit or explicit use of TO_CHAR(). If, however, the data type of LASTMODIFIED is _not_ DATE, then you have a serious design flaw. Dates should _always_ be typed as DATE or TIMESTAMP, See more at https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ — EdStevens, Dec 19 '20 at 14:00
@jdweng That is incorrect. In Oracle, a `DATE` data type is [stored in a table as 7-bytes](https://stackoverflow.com/questions/13568193/) representing century, year-of-century, month, day, hour, minute and second. — MT0, Dec 19 '20 at 16:07
@MT0 : It is still a number and the driver does the translation between the database and Net which has nothing to do with the question. The question has to do with displaying the number. — jdweng, Dec 20 '20 at 09:10

score 0 · Answer 1 · answered Dec 19 '20 at 10:32

0

In Oracle, you can format a date as a string with to_char():

select to_char(lastmodified, 'dd-mm-yyyy') as last_modified, name 
from v_vname 
where name = 'hector'

Side note: name in ('hector') is simpler phrased name = 'hector'.

answered Dec 19 '20 at 10:32

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Dec 19 '20 at 11:01

0

It's elegant when you use .ToString("dd-MM-yyyy") in C#

while (reader.Read())
{
    int date = reader.GetOrdinal("LASTMODIFIED"); // 0
    var LASTmTime = reader.GetValue(0).ToString("dd-MM-yyyy");
    Console.WriteLine("modifieddate:" + LASTmTime);
}

answered Dec 19 '20 at 11:01

Elton Bicalho do Carmo

131
2

score 0 · Answer 3 · answered Dec 19 '20 at 16:04

DATE data-type values in Oracle are binary values consisting of 7-bytes (which are always century, year-of-century, month, day, hour, minute and second).

What you are seeing when you say the format is DD-MON-RR is the user interface you are using formatting the binary value as something that you, the user, will understand and for SQL/Plus (and SQL Developer and others) this will be based on the NLS_DATE_FORMAT session parameter and the default NLS_DATE_FORMAT value depends on which territory you say you are using when you setup the database.

What you need to do, is the output a string representation of the DATE formatted according to your requirements. You can either do this in SQL and use TO_CHAR to format the string:

SELECT TO_CHAR( LASTMODIFIED, 'DD-MM-YYYY' ) AS last_modified,
       name
FROM   v_vname
WHERE  name = 'hector'

Or could do it in C#:

DateTime LASTmTime = reader.GetValue(0);
Console.WriteLine("modifieddate:" + LASTmTime.toString("dd-MM-yyyy"));

in oracle db format of date 04-DEC-20 while displaying in console shows date like 04-09-2020

3 Answers3