Home » SQL & PL/SQL » SQL & PL/SQL » How to check if time is attached with date? (10.2.0)
How to check if time is attached with date? [message #658007] Wed, 30 November 2016 04:41 Go to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Hi


When saving data, if date is truncated then time is set to 00:00:00 with the date.

I want to check that if there is any data n date column having time with it other than 00:00:00.

I ran following commands

select mod(trunc(sysdate)-hiredate, 1) 
from emp 
where mod(trunc(sysdate)-hiredate, 1) !=0;


select * 
from emp 
where trunc(hiredate)!=hiredate



Both above commands show NO ROWS as there is no time attached (other than the 00:00:00).



On my actual data, the report has given wrong result unless i ran the following command.

update mytable
set dateCol = trunc(dateCol);

I am very much puzzled as above two queries has resulted NO ROWS on my actual data but after running truncated command, everything went fine.


Note: the date range parameters given in report were END POINT of the dateCol data to cover the whole range and the "wrong resulted data" was lying between the end points.

WHat am i missing to check date and time relation ship?

Please guide.
Re: How to check if time is attached with date? [message #658008 is a reply to message #658007] Wed, 30 November 2016 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If that 2nd query returns no rows then all the dates have time of midnight.
I really can't see anyway you can be in the situation you say you're in unless you made a mistake - are you sure you checked the correct date column?
Are you sure that the report output changes aren't due to something other than the update?
Re: How to check if time is attached with date? [message #658009 is a reply to message #658008] Wed, 30 November 2016 05:48 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
I acheived my result on reports by following one of the way.
update mytable 
set dateCol = trunc(dateCol);

and the second was : In reports, date parameter query for FROM and TO date was changed by adding "trunc" (the date table was also having no time attached with dateCol)
select trunc(dateCol)
from dated;

The problemtic data present in myTable having a dateCol lies "well" between the FROM and TO date. (No way they are on end notes/points).

This all analysis has put me in deep trouble.


Re: How to check if time is attached with date? [message #658010 is a reply to message #658008] Wed, 30 November 2016 05:51 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
cookiemonster wrote on Wed, 30 November 2016 16:22

Are you sure that the report output changes aren't due to something other than the update?
Yes. As i ran the report BEFORE update and AFTER update . And my result was achieved afterward. (It was a trial balance which became balance at one. and difference of the trial balance was due to a customer account having code 5 and amount 60000. All the data relevant to code 5 customer was WELL BETWEEN IN THE INPUT date parameters in the report.)
Re: How to check if time is attached with date? [message #658013 is a reply to message #658010] Wed, 30 November 2016 07:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
FWIW - You can be sure & wrong at the same time.
Just remember that Oracle is correct more often than not & that all too frequently folks here incorrectly report "reality".

I suspect that Problem Exists Between Keyboard And Chair.
Re: How to check if time is attached with date? [message #658014 is a reply to message #658009] Wed, 30 November 2016 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
bluetooth420 wrote on Wed, 30 November 2016 06:48
I acheived my result on reports by following one of the way.
update mytable 
set dateCol = trunc(dateCol);
Please don't tell me you plan to run an update every time you run the report?
Re: How to check if time is attached with date? [message #658015 is a reply to message #658014] Wed, 30 November 2016 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The obvious thing to do is find a row that is excluded unless you fix the data and see exactly what datecol is set to for that row:
select to_char(datecol, 'DD-MM-YYYY HH24:MI:SS'), dump(datecol) from table where <approriate where clause>
Re: How to check if time is attached with date? [message #658022 is a reply to message #658013] Wed, 30 November 2016 13:49 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
BlackSwan wrote on Wed, 30 November 2016 18:53
FWIW - You can be sure & wrong at the same time.
Just remember that Oracle is correct more often than not & that all too frequently folks here incorrectly report "reality".

I suspect that Problem Exists Between Keyboard And Chair.

Thats why I am here to get feed back from you people

Quote:

bluetooth420 wrote on Wed, 30 November 2016 06:48
I acheived my result on reports by following one of the way.
update mytable
set dateCol = trunc(dateCol);
Please don't tell me you plan to run an update every time you run the report?

Obviously never


Re: How to check if time is attached with date? [message #658023 is a reply to message #658022] Wed, 30 November 2016 13:58 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
I dont know whats going. The restart of the PC has made the things OK. The result is OK what updating with truncate.

Though have to check with client's machine.

This problem has made me crazy
Re: How to check if time is attached with date? [message #658047 is a reply to message #658023] Thu, 01 December 2016 07:12 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Is the Datecol defined as a Date?
Re: How to check if time is attached with date? [message #658061 is a reply to message #658047] Thu, 01 December 2016 20:46 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

1. First thing check the column datatype is indeed date.

2. If you are finding your SELECT command for the count is giving no records but the update is saying some rows got updated then I would suspect between SELECT and UPDATE some other transaction has updated your table for the Date column in question might be through some scheduler which is running periodically like DBMS_JOB or through some other scheduler like Autosys or Ctrl-M which is something you have to investigate if there are any Updates happening to the same table for that particular date column from some other txns apart from your own txn.

garan
Re: How to check if time is attached with date? [message #658063 is a reply to message #658061] Fri, 02 December 2016 03:14 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
garan wrote on Fri, 02 December 2016 02:46

2. If you are finding your SELECT command for the count is giving no records but the update is saying some rows got updated
Well the update doesn't appear to have a where clause so it's always going to update all rows.
Of course it is still worth checking that no other process is messing with the data.
Previous Topic: ora-06504 PL/SQL: Return types of Result Set variables or query do not match
Next Topic: Element exists in a collection
Goto Forum:
  


Current Time: Thu Mar 28 15:03:41 CDT 2024