OraFAQ Forum: SQL & PL/SQL » to

Home » SQL & PL/SQL » SQL & PL/SQL » to_date in where clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to_date in where clause [message #685256]

Fri, 26 November 2021 05:27

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Table Structure
Fees 
Fmonth   Varchar2(10)
Tmonth   varchar2(10)

i want to compare my input date to character valus stored in table.


and to_char(fmonth)=to_char(to_date(:fd,'DD/MM/YYYY'),'Month')  -- fd is the runtime date values input mask:  DD-MON-YYYY
and to_char( tmonth)=to_char(to_date(:td,'DD/MM/YYYY'),'Month') -- td is the runtime date values input mask:  DD-MON-YYYY

Output is:

SQL> /

no rows selected

SQL>

please help me

Report message to a moderator

Re: to_date in where clause [message #685257 is a reply to message #685256]

Fri, 26 November 2021 05:32

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 04 November 2021 17:02

Lalit Kumar B wrote on Sat, 07 June 2014 19:01
...
Please post the insert statements for the tables.
Michel Cadot wrote on Wed, 01 October 2014 11:12

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you how to do it.
Michel Cadot wrote on Sun, 30 November 2014 08:14

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Michel Cadot wrote on Wed, 30 December 2015 10:36

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Michel Cadot wrote on Fri, 01 January 2016 11:26
Michel Cadot wrote on Wed, 30 December 2015 10:36

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
please help
Michel Cadot wrote on Fri, 01 April 2016 11:13

Once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Bill B wrote on Fri, 01 April 2016 16:39
What you want is easy to do, but doing is is different depending on what your oracle database is. Please type

select * from v$version;

and paste what is returned in this issue.
Michel Cadot wrote on Tue, 12 April 2016 08:10

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Michel Cadot wrote on Sat, 02 january 2021 13:35
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
(Just an extract of all reminders.)

Michel Cadot wrote on Thu, 28 October 2021 07:28

Michel Cadot wrote on Sat, 04 September 2021 16:33
Michel Cadot wrote on Wed, 28 July 2021 10:38

Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.
John Watson wrote on Sat, 04 September 2021 09:50
You have been asked, more than once, to follow the Forum rules: Format your posts correctly using [code] tags for code, and use type casting functions such as TO_DATE when working with dates and strings.

It us extremely rude of you to ignore these requests.
Your answer:

glmjoy wrote on Sat, 04 September 2021 10:05
sorry for that
You are so sorry that you ignore it in your next topic.

And post a test case, test it before but do NOT post the execution, just the statements, we can't copy and paste an execution.

In the end:

EdStevens wrote on Sat, 04 September 2021 16:45
You are trying to insert a character string ('01-JAN-2021') into a column that is defined as a DATE datatype. This will force an implied TO_DATE operation to convert that string into the internal, binary structure of a DATE. This may or may not succeed, depending on the controlling setting of NLS_DATE_FORMAT. Better to leave nothing to chance and explcitly use TO_DATE:
insert into TBAL values('121028',TO_DATE('01-JAN-2021',DD-MON-YYYY'),'01',2021);
Your answer:

glmjoy wrote on Sat, 04 September 2021 20:46
Thanks for the correction
but you ignore it.

Report message to a moderator

Re: to_date in where clause [message #685258 is a reply to message #685257]

Sat, 27 November 2021 01:13

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Test Case

create table family (fam_id number(4) primary key,fname varchar2(50),mob number(11));
create table fees (fam_id number(4) references family(fam_id),stuid number(4),sno number(4),fmonth varchar2(20),
                   tmonth varchar2(20),fyear number(4),fdate date,adm number(4),ftution  number(4),
                   ann number(4),arrear number(6),tot number(8),paid number(6),damt number(6),date2 date,
                   bal number(6));

insert into family VALUES (1,'ABC',3422222);
insert into family VALUES (2,'BC',3425622);
insert into family VALUES (3,'AC',342332);
insert into family VALUES (4,'ADD',3642211);
insert into family VALUES (5,'ADDBC',347262);
insert into family VALUES (6,'ABDC',342822);
insert into family VALUES (7,'ABDFC',342292);
insert into family VALUES (8,'AFGBC',3429922);
insert into family VALUES (9,'ABFFD',3422222);
COMMIT;
insert into fees VALUES (1,11,1,'September','September',2021, '01-SEP-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,11,2,'October','October',2021, '11-OCT-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,11,3,'November','November',2021, '07-NOV-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,12,1,'September','September',2021, '02-SEP-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,12,2,'October','October',2021, '08-OCT-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,12,3,'November','November',2021,'',0,1500,0,0,1500,0,0,'',0);
insert into fees VALUES (2,14,1,'September','September',2021, '01-SEP-21',0,1000,0,0,1000,1000,0,'',0);
insert into fees VALUES (2,14,2,'October','October',2021, '11-OCT-21',0,1000,0,0,1000,500,500,'07-NOV-21',0);
insert into fees VALUES (2,14,3,'November','November',2021, '04-NOV-21',0,1000,0,0,1000,1000,0,'',0);
insert into fees VALUES (3,15,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (3,15,2,'October','October',2021, '08-OCT-21',0,1200,0,0,1500,1500,0,'',0);
insert into fees VALUES (3,15,3,'November','November',2021,'10-NOV-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (3,16,1,'September','September',2021, '04-SEP-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (3,16,2,'October','October',2021, '02-OCT-21',0,1200,0,0,1500,1500,0,'',0);
insert into fees VALUES (3,16,3,'November','November',2021,'07-NOV-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (4,17,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0);
insert into fees VALUES (4,17,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0);
insert into fees VALUES (4,17,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,0,'18-NOV-21',0);
insert into fees VALUES (4,18,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0);
insert into fees VALUES (4,18,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'10-NOV-21',0);
insert into fees VALUES (4,18,3,'November','November',2021,'10-NOV-21',0,1100,0,0,1100,500,0,'10-NOV-21',0);
insert into fees VALUES (5,19,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0);
insert into fees VALUES (5,19,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0);
insert into fees VALUES (5,19,3,'November','November',2021,'10-NOV-21',0,1100,0,0,1100,500,0,'',0);
insert into fees VALUES (6,21,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0);
insert into fees VALUES (6,21,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0);
insert into fees VALUES (6,21,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,0,'18-NOV-21',0);
insert into fees VALUES (7,22,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0);
insert into fees VALUES (7,22,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0);
insert into fees VALUES (7,22,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,0,'18-NOV-21',0);
insert into fees VALUES (8,23,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (8,23,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,0,'',0);
insert into fees VALUES (8,23,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,600,'18-NOV-21',0);
COMMIT;

Report message to a moderator

Re: to_date in where clause [message #685259 is a reply to message #685258]

Sat, 27 November 2021 01:23

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

insert into fees VALUES (9,24,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,1200,0,'',0);
insert into fees VALUES (9,24,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'10-NOV-21',0);
insert into fees VALUES (9,24,3,'November','November',2021,'10-NOV-21',0,1100,0,0,1100,500,600,'18-NOV-21',0);
commit;

Report message to a moderator

Re: to_date in where clause [message #685260 is a reply to message #685258]

Sat, 27 November 2021 01:24

John Watson
Messages: 8803
Registered: January 2010
Location: Global Village

Senior Member

You are relying on implicit type casting. SQL is a strongly typed language: you must cast values into appropriate types before inserting or comparing. In this case, you are inserting a string into a date, with disastrous consequences:

orclz>
orclz> insert into fees VALUES (1,11,1,'September','September',2021, '01-SEP-21',0,1500,0,0,1500,1500,0,'',0);

1 row created.

orclz> select fdate from fees;

FDATE
-------------------
0001-09-21:00:00:00

orclz>

I do not think you meant the twentyfirst of September in the year one AD.

[Updated on: Sat, 27 November 2021 01:25]

Report message to a moderator

Re: to_date in where clause [message #685261 is a reply to message #685259]

Sat, 27 November 2021 01:30

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

please view the attached file.
/foru/forum/fa/14569/0/

Attachment: 123.jpg
(Size: 50.32KB, Downloaded 294 times)

Report message to a moderator

Re: to_date in where clause [message #685262 is a reply to message #685260]

Sat, 27 November 2021 01:32

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Quote:

but in my sql i am getting this result. form form 6i fdate format mask is : DD-MON-YY
01-SEP-21
11-OCT-21
04-NOV-21
02-SEP-21
08-OCT-21
-
01-SEP-21

10-NOV-21
02-SEP-21
08-OCT-21
10-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
39 rows selected.

Report message to a moderator

Re: to_date in where clause [message #685263 is a reply to message #685262]

Sat, 27 November 2021 01:34

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

i want to get the balance of student on min(fdate) and put this balacne into next paid date.

Report message to a moderator

Re: to_date in where clause [message #685265 is a reply to message #685263]

Sat, 27 November 2021 04:15

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post a valid test case AND your Oracle version as requested many and many times.

Report message to a moderator

Re: to_date in where clause [message #685267 is a reply to message #685265]

Sat, 27 November 2021 04:57

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Reports 6i
Oracle 11g
Test case already posted above

Report message to a moderator

Re: to_date in where clause [message #685268 is a reply to message #685267]

Sat, 27 November 2021 05:38

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not valid as John said and it has been repeated, showed and demonstrated many times to you.
Once more:

SQL> insert into fees VALUES (1,11,1,'September','September',2021, '01-SEP-21',0,1500,0,0,1500,1500,0,'',0);
insert into fees VALUES (1,11,1,'September','September',2021, '01-SEP-21',0,1500,0,0,1500,1500,0,'',0)
                                                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: to_date in where clause [message #685269 is a reply to message #685268]

Sat, 27 November 2021 06:05

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Quote:

https://livesql.oracle.com/apex/f?p=590:6:1045529513807::NO::P6_VIEW_OPTIONS:DESC&success_msg=U2NoZW1hIEluaXRpYWxpemVkLiBTY3JpcHQ gcnVuLCA2MCBzdGF0ZW1lbnRzIGF0.,dGVtcHRlZC4~%2Fwatl2BOCVn-HYlWYs6VfyvEsvc3B5EyC6m4ZqtZCKyDMxc_BpEMUgvKQnTNA6giyXc6p4EX5DMse4XWz9TRm7A& amp; amp;cs=115F4BDD9E360556C046A76E108B5EBE8

please check this link of live oracle sql.

Quote:

select fdate from fees
FDATE
01-SEP-21
11-OCT-21
04-NOV-21
02-SEP-21
08-OCT-21
-
01-SEP-21
11-OCT-21
07-NOV-21
02-SEP-21
08-OCT-21
-
01-SEP-21
11-OCT-21
04-NOV-21
02-SEP-21
08-OCT-21
10-NOV-21
04-SEP-21
02-OCT-21
07-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
02-SEP-21
08-OCT-21
10-NOV-21
02-SEP-21
08-OCT-21
10-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
02-SEP-21
08-OCT-21
15-NOV-21
Download CSV
39 rows selected.
Statement 54
COMMIT
Statement processed.
Statement 53
insert into fees VALUES (8,23,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,600,'18-NOV-21',0)
1 row(s) inserted.
Statement 52
insert into fees VALUES (8,23,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,0,'',0)
1 row(s) inserted.
Statement 51
insert into fees VALUES (8,23,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,1200,0,'',0)
1 row(s) inserted.
Statement 50
insert into fees VALUES (7,22,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,0,'18-NOV-21',0)
1 row(s) inserted.
Statement 49
insert into fees VALUES (7,22,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0)
1 row(s) inserted.
Statement 48
insert into fees VALUES (7,22,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0)
1 row(s) inserted.
Statement 47
insert into fees VALUES (6,21,3,'November','November',2021,'15-NOV-21',0,1100,0,0,1100,500,0,'18-NOV-21',0)
1 row(s) inserted.
Statement 46
insert into fees VALUES (6,21,2,'October','October',2021, '08-OCT-21',0,1100,0,0,1100,700,400,'15-NOV-21',0)
1 row(s) inserted.
Statement 45
insert into fees VALUES (6,21,1,'September','September',2021, '02-SEP-21',0,1200,0,0,1200,200,1000,'15-NOV-21',0)
1 row(s) inserted.

[Updated on: Sat, 27 November 2021 06:06]

Report message to a moderator

Re: to_date in where clause [message #685270 is a reply to message #685269]

Sat, 27 November 2021 08:04

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Some people smoke and have never a lung cancer so cigarette is safe.

Report message to a moderator

Re: to_date in where clause [message #685271 is a reply to message #685270]

Sat, 27 November 2021 13:59

Amine
Messages: 363
Registered: March 2010

Senior Member

Dear shahzad-ul-hasan,
You are registred on August 2002 and have posted 595 messages.

Normally, you're formatted to how an Orafaq post should be formatted.

It's clear : To have an accurate answer, post and accurate post and make the work of our Gurus easy.

Why are you complicating your life, an by the way complicating our Gurus life ?

I had a headache after reading your posts. Really...

Report message to a moderator

Re: to_date in where clause [message #685272 is a reply to message #685271]

Sat, 27 November 2021 20:46

shahzad-ul-hasan
Messages: 605
Registered: August 2002

Senior Member

Better sleep, it is above your level.

Report message to a moderator

Re: to_date in where clause [message #685288 is a reply to message #685256]

Sun, 05 December 2021 02:19

OraFerro
Messages: 415
Registered: July 2011

Senior Member

Dear Shahzad-ul-hasan,

just try to insert a date value in fdate instead of a string value.

insert into fees VALUES (8,23,3,'November','November',2021,TO_DATE('15-11-21','DD-MM-YYYY'),0,1100,0,0,1100,500,600,'18-NOV-21',0);

-- use to_date to convert the string into date

You already know how to use date masks in your where condition, just use the same while inserting.

Thanks,
Ferro

[Updated on: Sun, 05 December 2021 02:20]

Report message to a moderator

Previous Topic:	DBA_HIST_SQLTEXT string buffer too small
Next Topic:	Oracle Display parent, child, grandchild hierarchy

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 20 10:38:46 CDT 2023