Not a Valid month statement in oracle while querying -


i need in fixing 'not valid month' statement , below query working max function..

select max( y.file_sub_ret_date_time ) adpdba.work_request_file_trans_audit y y.file_event_type = 'submission' ,     y.file_submt_retrl_status = 'level1 posted' , y.file_sub_ret_date_time >= '01-dec-2015' , y.file_sub_ret_date_time <= '03-feb-2017' group y.wr_file_trans_info_id

and output followed...

12/11/2015 1:44:33 pm 2/2/2017 3:42:02 pm 2/2/2017 3:42:02 pm

now need rewrite query without max function , these queries prepared below. both display 'not valid month' statement..

select x.wr_file_trans_audit_id adpdba.work_request_file_trans_audit x x.file_sub_ret_date_time in  ('20170202034202','20170202034202','20151211014433')   select x.wr_file_trans_audit_id adpdba.work_request_file_trans_audit x x.file_sub_ret_date_time in  ('2017-02-02 03:42:02.0','2017-02-02 03:42:02.0','2015-12-11 01:44:33.0')

can me rid of error please...

i think problem in date format. date columns, oracle try convert date based on nls default settings. sql, let's more specific , tell oracle how interpret date string, like...

(assuming date data type)

select x.wr_file_trans_audit_id  adpdba.work_request_file_trans_audit x  x.file_sub_ret_date_time in       (to_date('2017-02-02 03:42:02', 'yyyy-mm-dd hh24:mi:ss'),        to_date('2017-02-02 03:42:02', 'yyyy-mm-dd hh24:mi:ss'),        to_date('2015-12-11 01:44:33', 'yyyy-mm-dd hh24:mi:ss')       );

..or saw comment on timestamp column

select x.wr_file_trans_audit_id  adpdba.work_request_file_trans_audit x  x.file_sub_ret_date_time in       (to_timestamp ('2017-02-02 03:42:02', 'yyyy-mm-dd hh24:mi:ss'),        to_timestamp ('2017-02-02 03:42:02', 'yyyy-mm-dd hh24:mi:ss'),        to_timestamp ('2015-12-11 01:44:33', 'yyyy-mm-dd hh24:mi:ss')       );

Comments

Post a Comment

Popular posts from this blog

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more