| |
|
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.
|
 |
12-03-12, 13:52
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 40
|
|
|
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
|
|
12-03-12, 14:32
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,092
|
|
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.
|
|
12-03-12, 14:34
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 588
|
|
|
|
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'.
|
|
12-03-12, 14:48
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 40
|
|
|
|
12-03-12, 15:11
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,092
|
|
Quote:
Originally Posted by stephaneeybert
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.
|
Last edited by anacedent; 12-03-12 at 15:42.
|
12-03-12, 15:42
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 40
|
|
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
|
|
|
12-03-12, 15:52
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,092
|
|
>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.
|
|
12-03-12, 16:05
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 40
|
|
It could be Hibernate maybe.
|
|
12-03-12, 16:13
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,092
|
|
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.
|
|
12-04-12, 03:24
|
|
Registered User
|
|
Join Date: Oct 2012
Posts: 40
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Trying to compare date part only of datetime between two timestamps
