Skip to Main Content
  • Questions
  • Expected Date format coming as 00:00:00 using DD/MM/YYYY HH24:MI:SS

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: April 20, 2017 - 11:16 am UTC

Last updated: April 22, 2017 - 1:47 am UTC

Version: Oracle 10g

Viewed 10K+ times! This question is

You Asked

Hi Team,

I am using the below query to get the records; which is working successfully. However when I am trying to get the time stamp in the column CREATED DATE then the 19/04/2017 00:00:00 & 3/4/2017 0:00.

select distinct b.transaction_id as "Common Ref.No.", b.vcn as VCN , d.agency_code as AgencyCode, c.AGENCY_NAME as companyname, a.port_code as portcode, a.name_of_stakeholder, b.created_by as userid, to_char(b.created_date,'DD/MM/YYYY HH24:MI:SS') as "CREATED DATE" from tpcs_stakeholder_registration a, tpcs_do_header b, tpcs_stkhol_header c, tpcs_stkhol_master d where b.created_by=a.name_of_stakeholder and a.pan = c.pan and a.pan=d.pan and c.pan=d.pan and a.stakeholder_group = c.stakeholder_group and a.PORT_CODE=c.PORT_CODE and b.SUBMITTED_DATE BETWEEN to_date ('2017-04-01', 'yyyy-mm-dd') and to_date ('2017-04-19', 'yyyy-mm-dd') and b.created_by <> 'tstsa01'

Need your help in getting the records for CREATED DATE column with correct time stamp.

Kindly check & confirm.

Thank you.

Regards,
rahul


and Connor said...

The DATE datatype can contain dates and times, and totally depends on what has been inserted into the table. For example

SQL> create table t ( id int, d date);

Table created.

SQL>
SQL> insert into t values ( 1, sysdate);

1 row created.

SQL> insert into t values ( 2, trunc(sysdate));

1 row created.

SQL> insert into t values ( 3, to_date('01-FEB-2017'));

1 row created.

SQL>
SQL> select id, to_char(d,'dd/mm/yyyy hh24:mi:ss') from t;

        ID TO_CHAR(D,'DD/MM/YY
---------- -------------------
         1 21/04/2017 10:58:34
         2 21/04/2017 00:00:00
         3 01/02/2017 00:00:00

3 rows selected.


For ID=2 and 3, I didnt include a time (which in fact, means "midnight") and hence it comes back out as 00:00:00 (ie, midnight)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 21, 2017 - 5:57 am UTC


Connor McDonald
April 22, 2017 - 1:47 am UTC

Sorry, I should have added something. If you want to get all rows between 2 dates A and B (which may or may not include time components), then your query can be like this:

where date_col >= trunc(to_date(A,'...'))
and   date_col <  trunc(to_date(B,'...')) + 1