How to check if time is attached with date? [message #658007] |
Wed, 30 November 2016 04:41 |
|
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 |
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 #658014 is a reply to message #658009] |
Wed, 30 November 2016 07:58 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
bluetooth420 wrote on Wed, 30 November 2016 06:48I 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 |
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 #658063 is a reply to message #658061] |
Fri, 02 December 2016 03:14 |
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.
|
|
|