Not a valid month.

string select_statement = "SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS";
string DateString1 = ActiveDateTime3.Date.Day.ToString() + "-" + ActiveDateTime3.Date.Month.ToString() + "-" + ActiveDateTime3.Date.Year.ToString();
string DateString2 = ActiveDateTime4.Date.Day.ToString() + "-" + ActiveDateTime4.Date.Month.ToString() + "-" + ActiveDateTime4.Date.Year.ToString();

               
switch (Group_RadioButtonList.SelectedIndex)
{
      case 0:
                select_statement += " Where RC_PAYMNB='" + txtPayment.Text + "'";
     break;
      case 1:
                select_statement += " Where RC_CASHNB='" + txtCashier.Text + "'";
     break;
       case 2:
                select_statement += " Where To_Date(RC_DATE, 'dd-mm-yyyy') <= '" + DateString2.ToString() + "' And To_Date(RC_DATE, 'dd-mm-yyyy') >= '" + DateString1.ToString() + "'";
//     Response.Write (select_statement);
                break;
      default : return;
}

"SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS Where To_Date(RC_DATE, 'dd-mm-yyyy') <= '1-11-2004' And To_Date(RC_DATE, 'dd-mm-yyyy') >= '1-10-2004'"

I get the following error:

ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month

Source Error:


Line 266:               OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(strSQLDDL,myConnection);
Line 267:               ddlDataSet1.Clear();  
Line 268:               myDataAdapter.Fill(ddlDataSet1);
Line 269:               myDataAdapter.Dispose();
Line 270:               myDataAdapter = null;
 

Source File: d:\inetpub\wwwroot\mspos\receipts.aspx.cs    Line: 268

Stack Trace:


[OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month
]
   System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode) +32
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +1189
   System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) +48
   System.Data.Odbc.OdbcCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
   MSPOS.Receipt.btnRunQuery_Click(Object sender, EventArgs e) in d:\inetpub\wwwroot\mspos\receipts.aspx.cs:268
   System.EventHandler.Invoke(Object sender, EventArgs e) +0
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1266

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Request Details
Session Id: l4wtwqffyghanj45tpuxxi45 Request Type: POST
Time of Request: 11/1/2004 3:08:41 PM Status Code: 500
Request Encoding: Unicode (UTF-8) Response Encoding: Unicode (UTF-8)
Trace Information
Category Message From First(s) From Last(s)
aspx.page Begin Init  
aspx.page End Init 0.000082 0.000082
aspx.page Begin LoadViewState 0.000111 0.000029
aspx.page End LoadViewState 0.000455 0.000344
aspx.page Begin ProcessPostData 0.000478 0.000023
aspx.page End ProcessPostData 0.000601 0.000123
aspx.page Begin ProcessPostData Second Try 0.000643 0.000042
aspx.page End ProcessPostData Second Try 0.000665 0.000023
aspx.page Begin Raise ChangedEvents 0.000695 0.000030
aspx.page End Raise ChangedEvents 0.000714 0.000019
aspx.page Begin Raise PostBackEvent 0.000730 0.000016
Unhandled Execution Error
ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month

  at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
  at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
  at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Odbc.OdbcCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
  at MSPOS.Receipt.btnRunQuery_Click(Object sender, EventArgs e) in d:\inetpub\wwwroot\mspos\receipts.aspx.cs:line 268
  at System.EventHandler.Invoke(Object sender, EventArgs e)
  at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
  at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
  at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
  at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
  at System.Web.UI.Page.ProcessRequestMain() 0.134705 0.133975
Control Tree
Control Id Type Render Size Bytes (including children) Viewstate Size Bytes (excluding children)
Cookies Collection
Name Value Size
ASP.NET_SessionId l4wtwqffyghanj45tpuxxi45 42
Headers Collection
Name Value
Cache-Control no-cache
Connection Keep-Alive
Content-Length 730
Content-Type application/x-www-form-urlencoded
Accept image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*
Accept-Encoding gzip, deflate
Accept-Language en-us
Authorization Negotiate TlRMTVNTUAADAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAABcKIogUCzg4AAAAP
Cookie ASP.NET_SessionId=l4wtwqffyghanj45tpuxxi45
Host localhost
Referer http://localhost/MSPOS/Receipts.aspx 
User-Agent Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)
Form Collection
Name Value
__VIEWSTATE dDwxMTUyMzA5MDMwO3Q8O2w8aTwxPjs+O2w8dDw7bDxpPDExPjtpPDEzPjtpPDE3Pjs+O2w8dDxwPGw8X2RhdGU7PjtsPFN5c3RlbS5EYXRlVGltZSwgbXNjb3JsaWIsIFZlcnNpb249MS4wLjUwMDAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iNzdhNWM1NjE5MzRlMDg5PDIwMDQtMTEtMDE+Oz4+Ozs+O3Q8cDxsPF9kYXRlOz47bDw1MDwyMDA0LTExLTAxPjs+Pjs7Pjt0PEAwPDs7Ozs7Ozs7Ozs+Ozs+Oz4+Oz4+O2w8TWFpbk1lbnU7TG9nb3V0MTppYm5Mb2dvdXQ7Pj58Y6Zc1sC9OSsp5s0upk7UXTuV7g==
Group_RadioButtonList StartDate
txtPayment  
txtCashier  
ActiveDateTime3 632348640000000000
ActiveDateTime3_day 1
ActiveDateTime3_month 10
ActiveDateTime3_year 2004
ActiveDateTime4 632348640000000000
ActiveDateTime4_day 1
ActiveDateTime4_month 11
ActiveDateTime4_year 2004
btnRunQuery Run Query
Server Variables
Name Value
ALL_HTTP HTTP_CACHE_CONTROL:no-cache HTTP_CONNECTION:Keep-Alive HTTP_CONTENT_LENGTH:730 HTTP_CONTENT_TYPE:application/x-www-form-urlencoded HTTP_ACCEPT:image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */* HTTP_ACCEPT_ENCODING:gzip, deflate HTTP_ACCEPT_LANGUAGE:en-us HTTP_AUTHORIZATION:Negotiate TlRMTVNTUAADAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAABcKIogUCzg4AAAAP HTTP_COOKIE:ASP.NET_SessionId=l4wtwqffyghanj45tpuxxi45 HTTP_HOST:localhost HTTP_REFERER:http://localhost/MSPOS/Receipts.aspx HTTP_USER_AGENT:Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)  
ALL_RAW Cache-Control: no-cache Connection: Keep-Alive Content-Length: 730 Content-Type: application/x-www-form-urlencoded Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */* Accept-Encoding: gzip, deflate Accept-Language: en-us Authorization: Negotiate TlRMTVNTUAADAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAABcKIogUCzg4AAAAP Cookie: ASP.NET_SessionId=l4wtwqffyghanj45tpuxxi45 Host: localhost Referer: http://localhost/MSPOS/Receipts.aspx User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)  
APPL_MD_PATH /LM/W3SVC/1/Root/MSPOS
APPL_PHYSICAL_PATH D:\Inetpub\wwwroot\MSPOS\
AUTH_TYPE  
AUTH_USER  
AUTH_PASSWORD  
LOGON_USER OMEGAMACHINE\Administrator
REMOTE_USER  
CERT_COOKIE  
CERT_FLAGS  
CERT_ISSUER  
CERT_KEYSIZE  
CERT_SECRETKEYSIZE  
CERT_SERIALNUMBER  
CERT_SERVER_ISSUER  
CERT_SERVER_SUBJECT  
CERT_SUBJECT  
CONTENT_LENGTH 730
CONTENT_TYPE application/x-www-form-urlencoded
GATEWAY_INTERFACE CGI/1.1
HTTPS off
HTTPS_KEYSIZE  
HTTPS_SECRETKEYSIZE  
HTTPS_SERVER_ISSUER  
HTTPS_SERVER_SUBJECT  
INSTANCE_ID 1
INSTANCE_META_PATH /LM/W3SVC/1
LOCAL_ADDR 127.0.0.1
PATH_INFO /MSPOS/Receipts.aspx
PATH_TRANSLATED D:\Inetpub\wwwroot\MSPOS\Receipts.aspx
QUERY_STRING  
REMOTE_ADDR 127.0.0.1
REMOTE_HOST 127.0.0.1
REMOTE_PORT 3025
REQUEST_METHOD POST
SCRIPT_NAME /MSPOS/Receipts.aspx
SERVER_NAME localhost
SERVER_PORT 80
SERVER_PORT_SECURE 0
SERVER_PROTOCOL HTTP/1.1
SERVER_SOFTWARE Microsoft-IIS/6.0
URL /MSPOS/Receipts.aspx
HTTP_CACHE_CONTROL no-cache
HTTP_CONNECTION Keep-Alive
HTTP_CONTENT_LENGTH 730
HTTP_CONTENT_TYPE application/x-www-form-urlencoded
HTTP_ACCEPT image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*
HTTP_ACCEPT_ENCODING gzip, deflate
HTTP_ACCEPT_LANGUAGE en-us
HTTP_AUTHORIZATION Negotiate TlRMTVNTUAADAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAAAAAAAEgAAAAAAAAASAAAAAAAAABIAAAABcKIogUCzg4AAAAP
HTTP_COOKIE ASP.NET_SessionId=l4wtwqffyghanj45tpuxxi45
HTTP_HOST localhost
HTTP_REFERER http://localhost/MSPOS/Receipts.aspx 
HTTP_USER_AGENT Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)

mathieu_cuprykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pek99Commented:
The problem is your RC_date column. What type is it ?

If DATE, then the function TO_DATE is not to be used.

If it is char, varchar etc. then there are some rows which do not have valid format i.e.  99-99-9999.
Note that all digits and separators must be present !
0
mathieu_cuprykAuthor Commented:
RC_DATE is dd-MM-yyyy
0
pek99Commented:
then make a following test, please:

With interactive SQL do

select distinct substr(rc_date,4,2) from TBRECEIPTS;

you should obtain twelve rows containing '01', '02' ... '12'.

If it looks good, try tu rerun your select command interactively with to_date on both sides of comparison:

SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where To_Date(RC_DATE, 'DD-MM-YYYY') BETWEEN TO_DATE('01-10-2004,'DD-MM-YYYY') And To_Date('01-11-2004','DD-MM-YYYY');

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hanzpkCommented:
there might be a difference setting in your server and client running the query ( NLS setting  about date )
0
mathieu_cuprykAuthor Commented:
SQL*Plus: Release 9.2.0.1.0 - Production on Lu Nov 1 22:07:49 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected.

SU
--
10

1 row selected.

0
mathieu_cuprykAuthor Commented:
So thing is wrong?
0
paquicubaCommented:
It's just a formatting issue, see below:

SQL> select * from testing where to_date(dt,'dd/mm/yyyy') <= '2-12-2004';
select * from testing where to_date(dt,'dd/mm/yyyy') <= '2-12-2004'
                                                        *
ERROR at line 1:
ORA-01843: not a valid month


SQL> alter session set nls_date_format='DD/MM/YYYY';

Session altered.

SQL> select * from testing where to_date(dt,'dd/mm/yyyy') <= '2-12-2004';

DT
----------
02-12-2004


You won't be able to do the conversion unless you store your dates in the same format specified by the database's
init parameter "NLS_DATE_FORMAT":

SQL> SELECT * FROM TESTING WHERE TO_DATE(DT,'DD/MM/YYYY') <= '02-12-2004';
SELECT * FROM TESTING WHERE TO_DATE(DT,'DD/MM/YYYY') <= '02-12-2004'
                                                        *
ERROR at line 1:
ORA-01843: not a valid month


SQL> SELECT * FROM TESTING WHERE TO_DATE(DT,'DD/MM/YYYY') <= '02-DEC-2004';

DT
--------------------
02-12-2004
02-DEC-2004

Do the following in SQL*Plus:

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
02-NOV-04 <<--- This format is the one causing all the problems


alter session set nls_date_format='DD/MM/YYYY';


SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
----------
02/11/2004  <<--- This new format, which only applies to the active session should do it


SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where To_Date(RC_DATE,'DD/MM/YYYY') <= '1-11-2004' And To_Date(RC_DATE,'DD/MM/YYYY') >= '1-10-2004'


If NLS_DATE_FORMAT is not set in INIT.ORA, the session NLS_DATE_FORMAT will default to DD-MON-YY, so
you may consider setting NLS_DATE_FORMAT TO 'DD/MM/YYYY' in your INIT.ORA

Hope this helps!
0
luttappi123Commented:
hi,
   also check the date/regional setting of the OS in both client and Server. It seems to be a date format issue.

Thanks
Luttappi
0
mathieu_cuprykAuthor Commented:
u are right but how do u check in oracle server
0
luttappi123Commented:

   SELECT NLS_DATE_FORMAT FROM NLS_DATABASE_PARAMETERS;

0
mathieu_cuprykAuthor Commented:

SQL*Plus: Release 9.2.0.1.0 - Production on Ma Nov 2 08:28:21 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected.
SELECT NLS_DATE_FORMAT FROM NLS_DATABASE_PARAMETERS
       *
ERROR at line 1:
ORA-00904: "NLS_DATE_FORMAT": invalid identifier
0
luttappi123Commented:
i am really sorry, it is

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mathieu_cuprykAuthor Commented:
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

1 row selected.


Now this is not good?
0
mathieu_cuprykAuthor Commented:
What should I do?
0
luttappi123Commented:

   It is exactly what u expect to see. there is no problem with you nls_date_format parameter.
what is the datatype of RC_DATE column?

could u try the following querry

"SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where To_Date(RC_DATE, 'dd-mm-yyyy') <= '1-nov-2004' And To_Date(RC_DATE, 'dd-mm-yyyy') >= '1-nov-2004'"



0
mathieu_cuprykAuthor Commented:
RC_DATA is varchar(19)
and the date format is not conformed yet,
but it is dd-MM-yyyy.
0
luttappi123Commented:
is the query i gave u is working?

from the sql promt try the following

select to_date(rc_date,'dd,mm-yyyy') from tbreceipts.

what is the output of the above querry?

0
mathieu_cuprykAuthor Commented:
TO_DATE(
--------
10/10/04
11/10/04
12/10/04
13/10/04
14/10/04
15/10/04
16/10/04
17/10/04

8 rows selected.
0
luttappi123Commented:
ok, try this

"SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where To_Date(RC_DATE, 'dd-mm-yyyy') <= to_date('1-11-2004', 'dd-mm-yyyy') And To_Date(RC_DATE, 'dd-mm-yyyy') >= to_date('1-11-2004', 'dd-mm-yyyy')"
0
mathieu_cuprykAuthor Commented:
no rows selected.

0
luttappi123Commented:
hi
There is no problem with the syntax now. it is the problem with the logic. if you want to see all the rows with rc_date older than 1-11-2004 try this

SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where To_Date(RC_DATE, 'dd-mm-yyyy') <= to_date('1-11-2004', 'dd-mm-yyyy');

I really dont know what u try to accomplish here giving the same date which will natuarlly give 0 number of rows as now row will ever satisfy the condition.
0
mathieu_cuprykAuthor Commented:
RC_PAYMNB  RC_CASHNB RC_DATE
---------- ---------- -------------------
RC_DATA

  42884297     888888 10-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42884298     888888 11-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42884299     888888 12-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885300     888888 13-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885301     888888 14-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885302     888888 15-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885303     888888 16-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885304     888888 17-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n


8 rows selected.

0
mathieu_cuprykAuthor Commented:
Can u fix the case statement for an interval:

string select_statement = "SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS";
string DateString1 = ActiveDateTime3.Date.Day.ToString() + "-" + ActiveDateTime3.Date.Month.ToString() + "-" + ActiveDateTime3.Date.Year.ToString();
string DateString2 = ActiveDateTime4.Date.Day.ToString() + "-" + ActiveDateTime4.Date.Month.ToString() + "-" + ActiveDateTime4.Date.Year.ToString();



select_statement += " Where To_Date(RC_DATE, 'dd-mm-yyyy') <= '" + DateString2.ToString() + "' And To_Date(RC_DATE, 'dd-mm-yyyy') >= '" + DateString1.ToString() + "'";

I will give you 1500 points. I have this question posted everywhere.
0
paquicubaCommented:
Try this one:
SQL> SELECT * FROM TESTING;

DT
--------------------
02-12-2004
03-12-2004
05-12-2004
06-12-2004
01-12-2004
30-11-2004

6 rows selected.

Elapsed: 00:00:00.00
SQL> SELECT * FROM TESTING WHERE TO_CHAR(TO_DATE(DT,'DD/MM/YYYY'),'YYMMDD') <= '041202';

DT
--------------------
02-12-2004
01-12-2004
30-11-2004

Yours looks like:
SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA FROM TBRECEIPTS
Where to_char(To_Date(RC_DATE,'dd/mm/yyyy'),'yymmdd') <= '041101'
And To_char(To_Date(RC_DATE, 'dd/mm/yyyy'),'yymmdd') >= '041001'
0
mathieu_cuprykAuthor Commented:
SELECT * FROM TBRECEIPTS WHERE TO_CHAR(TO_DATE(RC_DATE,'DD/MM/YYYY'),'YYMMDD') <= '041202';

Gives me

---------- ---------- -------------------
RC_DATA

  42884297     888888 10-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42884298     888888 11-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42884299     888888 12-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885300     888888 13-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885301     888888 14-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885302     888888 15-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885303     888888 16-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n

  42885304     888888 17-10-2004
T S T T        \n\nIND.SQUARE/EDWARD ST\n\nVAT REG# 100476     \n\nBill Payment  2004/07/1          100.00 \n\nAccount:   91013698-0000                \n\n      Chantal Rigaud                    \n\nA/c Balance:  2,566.33                  \n\n                                        \n\n                                        \n\nTOTAL                            100.00 \n\n                                        \n\nTENDERED                                \n\n Cash                   100.00          \n\n    TOTAL TENDERED               100.00 \n\n                                        \n\nCHANGE                             0.00 \n\n


8 rows selected.


So how should I fix. U can open another link to the other forums where I ask this question if not I will ask this question again.
0
luttappi123Commented:
Change the following string as shown below and try.

select_statement += " Where To_Date(RC_DATE, 'dd-mm-yyyy') <= '" +
to_date(DateString2.ToString(), 'dd-mm-yyyy') + "' And To_Date(RC_DATE, 'dd-mm-yyyy') >= '" + to_date(DateString1.ToString(), 'dd-mm-yyyy') + "'";
0
mathieu_cuprykAuthor Commented:
D:\Inetpub\wwwroot\MSPOS\Receipts.aspx.cs(253): Too many characters in character literal
To_Date(DateString2, 'dd-mm-yyyy')
                                **********
0
luttappi123Commented:
select_statement += " Where " + To_Date(RC_DATE, 'dd-mm-yyyy') + " <= " +
to_date(DateString2.ToString(), 'dd-mm-yyyy') + " And " + To_Date(RC_DATE, 'dd-mm-yyyy') + " >= " + to_date(DateString1.ToString(), 'dd-mm-yyyy')  ;

i hope the DateString2.ToString() is returning character.
0
hanzpkCommented:
ever try this?
SELECT RC_PAYMNB, RC_CASHNB, RC_DATE, RC_DATA
FROM TBRECEIPTS Where
To_Date(RC_DATE, 'dd/mon/rrrr') between '01/nov/2004'  and '01/oct/2004'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.