If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Trying to compare date part only of datetime between two timestamps

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 42
Trying to compare date part only of datetime between two timestamps

select this_.id as id45_0_, this_.version as version45_0_, this_.firstname as firstname45_0_, this_.lastname as lastname45_0_, this_.email as email45_0_, this_.text_comment as text6_45_0_, this_.country as country45_0_, this_.subscribe as subscribe45_0_, this_.imported as imported45_0_, this_.creation_datetime as creation10_45_0_ from mail_address this_ where to_date(creation_datetime, 'YYYY-MM-DD') between to_date('2012-12-03 19:49:05.643', 'YYYY-MM-DD') and to_date('2012-12-04 19:49:05.643', 'YYYY-MM-DD') order by this_.firstname asc, this_.lastname asc, this_.email asc

I get an error message:

ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,592
Code:
SELECT this_.id                AS id45_0_, 
       this_.version           AS version45_0_, 
       this_.firstname         AS firstname45_0_, 
       this_.lastname          AS lastname45_0_, 
       this_.email             AS email45_0_, 
       this_.text_comment      AS text6_45_0_, 
       this_.country           AS country45_0_, 
       this_.subscribe         AS subscribe45_0_, 
       this_.imported          AS imported45_0_, 
       this_.creation_datetime AS creation10_45_0_ 
FROM   mail_address this_ 
WHERE  To_date(creation_datetime, 'YYYY-MM-DD') BETWEEN 
              To_date('2012-12-03 19:49:05.643', 'YYYY-MM-DD') AND To_date( 
              '2012-12-04 19:49:05.643', 'YYYY-MM-DD') 
ORDER  BY this_.firstname ASC, 
          this_.lastname ASC, 
          this_.email ASC
what datatype is creation_datetime?

when all else fails Read The Fine Manual

TO_DATE
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 615
Code:
between
to_date('2012-12-03 19:49:05.643',
        'YYYY-MM-DD') and
to_date('2012-12-04 19:49:05.643',
        'YYYY-MM-DD')
Does it appear to you that values and format masks are matching? How would you convert that string based only on that mask?
If you want to extract only part of that DATE (e.g. day with hour), use proper function (SUBSTR for extracting the VARCHAR2 expression, TRUNC for truncating the DATE one). In this case: why are you using time part in those VARCHAR2 constants when you decide to ignore them?

By the way, DATE data type does not contain fractions of second. TIMESTAMP data type does.

As you did not specify what is data type of CREATION_DATETIME, it is impossible to propose more.
Just in case it is VARCHAR2: enjoy permanent correction of its values having invalid format or not representing any date/time, e.g. '2012-12-04 19:69:05.643'.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 42
It is a date type.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,592
Quote:
Originally Posted by stephaneeybert View Post
It is a date type.
NEVER use TO_DATE on existing DATE datatype

> To_date(creation_datetime, 'YYYY-MM-DD') BETWEEN

consider to RTFM & use TO_DATE properly; supplied mask must match provided string.

>Trying to compare date part only of datetime between two timestamps

TIMESTAMP & DATE are two different datatypes. so title should be as below

Trying to compare date part only of datetime between two DATES.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.

Last edited by anacedent; 12-03-12 at 15:42.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 42
Hello,

Sorry for the bother, I did read the manual and now understand a bit more, hoping to stop making a fool of myself, trying to go for this thing:

Code:
DateTimeFormatter dateTimeFormatter = DateTimeFormat.forPattern("YYYY-MM-dd");
criteria.add(Restrictions.sqlRestriction("trunc(creation_datetime, 'YYYY-MM-DD') between to_date('" + dateTimeFormatter.print(fromDateTime) + "', 'YYYY-MM-DD') and to_date('" + dateTimeFormatter.print(toDateTime) + "', 'YYYY-MM-DD')"));
criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).addOrder(Order.asc("email"));
The Maven build says:

Quote:
-------------------------------------------------------------------------------
Test set: com.thalasoft.learnintouch.core.dao.MailAddressDao Test
-------------------------------------------------------------------------------
Tests run: 13, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 16.971 sec <<< FAILURE!
testFindWithCreationDateTime(com.thalasoft.learnin touch.core.dao.MailAddressDaoTest) Time elapsed: 0.124 sec <<< ERROR!
org.springframework.dao.DataIntegrityViolationExce ption: could not execute query; SQL [select this_.id as id45_0_, this_.version as version45_0_, this_.firstname as firstname45_0_, this_.lastname as lastname45_0_, this_.email as email45_0_, this_.text_comment as text6_45_0_, this_.country as country45_0_, this_.subscribe as subscribe45_0_, this_.imported as imported45_0_, this_.creation_datetime as creation10_45_0_ from mail_address this_ where trunc(creation_datetime, 'YYYY-MM-DD') between to_date('2012-12-03', 'YYYY-MM-DD') and to_date('2012-12-04', 'YYYY-MM-DD') order by this_.firstname asc, this_.lastname asc, this_.email asc]; nested exception is org.hibernate.exception.DataException: could not execute query
But running the sql statement on the command line seems to be okay:

Quote:
SQL> select this_.id as id45_0_, this_.version as version45_0_, this_.firstname as firstname45_0_, this_.lastname as lastname45_0_, this_.email as email45_0_, this_.text_comment as text6_45_0_, this_.country as country45_0_, this_.subscribe as subscribe45_0_, this_.imported as imported45_0_, this_.creation_datetime as creation10_45_0_ from mail_address this_ where trunc(creation_datetime, 'YYYY-MM-DD') between to_date('2012-12-03', 'YYYY-MM-DD') and to_date('2012-12-04', 'YYYY-MM-DD') order by this_.firstname asc, this_.lastname asc, this_.email asc;

no rows selected
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,592
>But running the sql statement on the command line seems to be okay:
so the issue is between you & Maven & does not involve Oracle.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 42
It could be Hibernate maybe.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,592
can you do either or both as below via Hibernate

select count(*) from user_objects;
select sysdate from dual;
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2012
Posts: 42
I'm trying to, will come back with the result when I have some.

Note that lots of other integration tests, in the same test suite, work fine against the same Oracle database instance.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On