Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Not a valid month.

Posted on 2004-11-01
29
Medium Priority
?
3,123 Views
Last Modified: 2012-05-05
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)

0
Comment
Question by:mathieu_cupryk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 9
  • 2
  • +2
29 Comments
 
LVL 2

Expert Comment

by:pek99
ID: 12467965
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
 

Author Comment

by:mathieu_cupryk
ID: 12467976
RC_DATE is dd-MM-yyyy
0
 
LVL 2

Expert Comment

by:pek99
ID: 12468260
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Expert Comment

by:hanzpk
ID: 12469182
there might be a difference setting in your server and client running the query ( NLS setting  about date )
0
 

Author Comment

by:mathieu_cupryk
ID: 12469689
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
 

Author Comment

by:mathieu_cupryk
ID: 12469717
So thing is wrong?
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12470252
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
 
LVL 1

Expert Comment

by:luttappi123
ID: 12470584
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
 

Author Comment

by:mathieu_cupryk
ID: 12472260
u are right but how do u check in oracle server
0
 
LVL 1

Assisted Solution

by:luttappi123
luttappi123 earned 2000 total points
ID: 12472329

   SELECT NLS_DATE_FORMAT FROM NLS_DATABASE_PARAMETERS;

0
 

Author Comment

by:mathieu_cupryk
ID: 12472340

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
 
LVL 1

Accepted Solution

by:
luttappi123 earned 2000 total points
ID: 12472374
i am really sorry, it is

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

Author Comment

by:mathieu_cupryk
ID: 12472431
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

1 row selected.


Now this is not good?
0
 

Author Comment

by:mathieu_cupryk
ID: 12472501
What should I do?
0
 
LVL 1

Expert Comment

by:luttappi123
ID: 12472734

   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
 

Author Comment

by:mathieu_cupryk
ID: 12472765
RC_DATA is varchar(19)
and the date format is not conformed yet,
but it is dd-MM-yyyy.
0
 
LVL 1

Expert Comment

by:luttappi123
ID: 12472808
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
 

Author Comment

by:mathieu_cupryk
ID: 12472844
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
 
LVL 1

Expert Comment

by:luttappi123
ID: 12472960
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
 

Author Comment

by:mathieu_cupryk
ID: 12472977
no rows selected.

0
 
LVL 1

Expert Comment

by:luttappi123
ID: 12473077
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
 

Author Comment

by:mathieu_cupryk
ID: 12473343
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
 

Author Comment

by:mathieu_cupryk
ID: 12473362
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 12475016
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
 

Author Comment

by:mathieu_cupryk
ID: 12475495
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
 
LVL 1

Expert Comment

by:luttappi123
ID: 12480266
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
 

Author Comment

by:mathieu_cupryk
ID: 12480575
D:\Inetpub\wwwroot\MSPOS\Receipts.aspx.cs(253): Too many characters in character literal
To_Date(DateString2, 'dd-mm-yyyy')
                                **********
0
 
LVL 1

Expert Comment

by:luttappi123
ID: 12480638
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
 
LVL 1

Expert Comment

by:hanzpk
ID: 12489516
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

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question