Home » SQL & PL/SQL » SQL & PL/SQL » Trim time from date field
Trim time from date field [message #255777] Wed, 01 August 2007 14:14 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
RHEL4 Oracle 10.2

My nls_date_format is:
SQL> select sysdate from dual;

SYSDATE
-------------------
2007-08-01 15:07:28

I'm attempting to search by a date and group by that date, however without including the time portion of the date field:

select to_CHAR(addeddate, 'YYYY-MM-DD'), count(*) from members where websiteid=1453
and to_CHAR(addeddate, 'YYYY-MM-DD') >= '2007-08-01'
group by addeddate;

Since the addeddate field is updated and populated with the date/time format, how can I query the addeddate field and group by a date without the time?

For instance, I want the query above to produce the output:
ADDEDDATE COUNT(*)
--------- --------
2007-08-01 101

Currently, the query produces:

TO_CHAR(ADDEDDATE,'YYYY-MM-DD' COUNT(*)
----------------------------- ------
2007-08-01 1
2007-08-01 1
2007-08-01 1
2007-08-01 1


Thanks.
Re: Trim time from date field [message #255783 is a reply to message #255777] Wed, 01 August 2007 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
TRUNC(ADDEDDATE)
Re: Trim time from date field [message #255790 is a reply to message #255783] Wed, 01 August 2007 14:47 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I tried this three different ways:

SQL> select trunc(to_CHAR(addeddate, 'YYYY-MM-DD')), count(*) from members where websiteid=1453
and to_CHAR(addeddate, 'YYYY-MM-DD') >= '2007-08-01'
group by addeddate;
select trunc(to_CHAR(addeddate, 'YYYY-MM-DD')), count(*) from members where websiteid=1453
*
ERROR at line 1:
ORA-01722: invalid number


and also:

select trunc(addeddate), count(*) from members where websiteid=1453
and trunc(addeddate) >= '2007-08-01'
group by addeddate;

This second attempt produces the same output posted in my initial query:

TRUNC(ADDEDDATE) COUNT(*)
----------------------------- ------
2007-08-01 1
2007-08-01 1
2007-08-01 1
2007-08-01 1

This thrid attempt also provides the above output:

select trunc(addeddate), count(*) from members where websiteid=1453
and to_CHAR(addeddate, 'YYYY-MM-DD HH:MI:SS') >= '2007-08-01 00:00:00'
group by addeddate;

Thanks.

[Updated on: Wed, 01 August 2007 14:47]

Report message to a moderator

Re: Trim time from date field [message #255791 is a reply to message #255777] Wed, 01 August 2007 14:50 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You are currently using...

select to_CHAR(addeddate, 'YYYY-MM-DD'), count(*) from members where websiteid=1453
and to_CHAR(addeddate, 'YYYY-MM-DD') >= '2007-08-01'
group by addeddate;



Try this and see if it does what you want...

select trunc(addeddate), count(*) 
from   members 
where  websiteid=1453
  and  trunc(addeddate) >= to_date('2007-08-01','YYYY-MM-DD')
group by addeddate;



I have found that it's always better to convert strings to dates for comparison rather than dates to strings.
Re: Trim time from date field [message #255793 is a reply to message #255791] Wed, 01 August 2007 14:53 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Unfortunately, that still produces the same result:

select trunc(addeddate), count(*)
from members
where websiteid=1453
and trunc(addeddate) >= to_date('2007-08-01','YYYY-MM-DD')
group by addeddate;

TRUNC(ADDEDDATE) COUNT(*)
------------------- ----------
2007-08-01 00:00:00 1
2007-08-01 00:00:00 1
2007-08-01 00:00:00 1
2007-08-01 00:00:00 1
Re: Trim time from date field [message #255795 is a reply to message #255777] Wed, 01 August 2007 14:54 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
doesn't the GROUP BY need to be ..

GROUP BY  trunc(addeddate)

?
Re: Trim time from date field [message #255796 is a reply to message #255795] Wed, 01 August 2007 14:56 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
SWEET MERCY!!! THAT'S IT!!

SQL> select trunc(addeddate), count(*)
from members
where websiteid=1453
and trunc(addeddate) >= to_date('2007-07-31','YYYY-MM-DD')
group by trunc(addeddate); 2 3 4 5

TRUNC(ADDEDDATE) COUNT(*)
------------------- ----------
2007-07-31 00:00:00 155
2007-08-01 00:00:00 108


That was interesting. Thanks for your help.
Previous Topic: select systimestamp + tz_offset('US/Central') from dual;
Next Topic: Doubt in executing PL/SQL procedure
Goto Forum:
  


Current Time: Tue Dec 06 16:16:12 CST 2016

Total time taken to generate the page: 0.13727 seconds