Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Problem 2

Status
Not open for further replies.
ekta22

ekta22

IS-IT--Management
May 3, 2004
359
US
Hi,

I have a field in my database that stores both date and time. I have formula where I do date comparisons but I only want to use the date part and ignore the time. I don't want to convert the date to string by using TO_CHAR. How can I do that?
 
Sort by date Sort by votes
Ekta,
Code: 
...WHERE TRUNC(<some date>) = TRUNC(SYSDATE)...
...for example.

Let us know if you have additional questions on this topic.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
will try that out.. so TRUNC gets rid of the time portion for the date time field? How about using to_date on a date field? Or to_date can only be used on a string?

thanks
 
Upvote 0 Downvote
Ekta said:
so TRUNC gets rid of the time portion for the date time field?
Click to expand...
All DATE expressions in Oracle always contain a TIME component, even after a TRUNC has done its work. In the case of a TRUNC(<date>), the TIME component becomes "00:00:00" (i.e., midnight at the beginning of a particular DATE).


So, when you apply the TRUNC function against a DATE, it doesn't really TRUNCATE a DATE, it simply causes the DATE/TIME to "snap" (round down) to a beginning value. In the example, above, TRUNC causes the DATE to "snap" to the beginning of the DAY (i.e., "00:00:00").

You may also use the TRUNC function to "snap" to the beginning of other time boundaries, as well:
Code: 
SQL> col a format a24 heading "Current Date/Time"
SQL> col b format a24 heading "TRUNC'd Date/Time"
SQL> REM Mask: 'mi' = Snap down to 'Minute' boundary
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'mi'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:23 Tue, 2007-12-04 13:13:00
SQL> prompt

SQL> REM Mask: 'hh' = Snap down to 'Hour' boundary
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'hh'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:23 Tue, 2007-12-04 13:00:00
SQL> prompt

SQL> REM Mask: none = Snap down to 'Current Day' boundary (same as using 'DD')
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:23 Tue, 2007-12-04 00:00:00
SQL> prompt

SQL> REM Mask: 'd' = Snap down to 'Beginning of the Calendar Week' boundary
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'D'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:24 Sun, 2007-12-02 00:00:00
SQL> prompt

SQL> REM Mask: 'W' = Snap down to 'Beginning of the Week From 1st Day of Month'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'w'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:24 Sat, 2007-12-01 00:00:00
SQL> prompt

SQL> REM Mask: 'WW' = Snap down to 'Beginning of the Week From 1st Day of Year'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'wW'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:24 Mon, 2007-12-03 00:00:00
SQL> prompt

SQL> REM Mask: 'MM' = Snap down to '1st of the Month'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'MM'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:25 Sat, 2007-12-01 00:00:00
SQL> prompt

SQL> REM Mask: 'Q' = Snap down to '1st Day of the Calendar Quarter'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'Q'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:25 Mon, 2007-10-01 00:00:00
SQL> prompt

SQL> REM Mask: 'Y' or 'YY' or 'YYY' or 'YYYY'= Snap down to '1st Day of the Year'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'Y'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:25 Mon, 2007-01-01 00:00:00
SQL> prompt

SQL> REM Mask: 'CC' = Snap down to '1st Day of the Century'
SQL> select to_char(sysdate,'Dy, YYYY-mm-dd hh24:mi:ss') a
  2        ,to_char(trunc(sysdate,'CC'),'Dy, YYYY-mm-dd hh24:mi:ss') b
  3    from dual
  4  /

Current Date/Time        TRUNC'd Date/Time
------------------------ ------------------------
Tue, 2007-12-04 13:13:26 Mon, 2001-01-01 00:00:00
SQL>

Ekta said:
How about using to_date on a date field? Or to_date can only be used on a string?
Click to expand...
Oracle allows the TO_DATE function on character strings only, not on DATE expressions (since DATEs are already DATEs <grin>).


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Dave, as always a very full and helpful explanation and set of demos (as always above and beyond :) )
Might I suggest that you might be able to simplify your demo by removing the to_chars and alter the demo session's nls_date_format parameter in order to display the time elements as you desire (just a suggestion :) )
 
Upvote 0 Downvote
That's a great suggestion, Jim! I shall do that should the occasion arise again. Thanks.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Thanks a lot Dave. That post was really helpful.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
sqlPower
  • Locked
  • Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
753
SkipVought
S
EM1107
  • Locked
  • Question
Insert date and time using UTC time in a DATE column
Replies
14
Views
988
EM1107
EM1107
kernal
  • Locked
  • Question
If Day of the Week='Y' (i.e. Mon='Y') and between a Start Date and End Date then Count #of Days 1
Replies
1
Views
158
Beilstwh
Beilstwh
kernal
  • Locked
  • Question
Change DATE and also a TIMESTAMP to just a date as 'MM/DD/YYYY. Also, need dummy date when null 1
Replies
2
Views
138
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top