Results 1 to 10 of 10
  1. #1
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    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'.

  4. #4
    Join Date
    Oct 2012
    Posts
    42
    It is a date type.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    Last edited by anacedent; 12-03-12 at 16:42.
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  6. #6
    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:

    -------------------------------------------------------------------------------
    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:

    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

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    >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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  8. #8
    Join Date
    Oct 2012
    Posts
    42
    It could be Hibernate maybe.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  10. #10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •