Solved

Not a valid month.

Posted on 2004-11-01
2,964 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
Question by:mathieu_cupryk
    29 Comments
     
    LVL 2

    Expert Comment

    by:pek99
    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
    RC_DATE is dd-MM-yyyy
    0
     
    LVL 2

    Expert Comment

    by:pek99
    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
     
    LVL 1

    Expert Comment

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

    Author Comment

    by:mathieu_cupryk
    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
    So thing is wrong?
    0
     
    LVL 23

    Expert Comment

    by:paquicuba
    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
    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
    u are right but how do u check in oracle server
    0
     
    LVL 1

    Assisted Solution

    by:luttappi123

       SELECT NLS_DATE_FORMAT FROM NLS_DATABASE_PARAMETERS;

    0
     

    Author Comment

    by:mathieu_cupryk

    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:
    i am really sorry, it is

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

    Author Comment

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

    1 row selected.


    Now this is not good?
    0
     

    Author Comment

    by:mathieu_cupryk
    What should I do?
    0
     
    LVL 1

    Expert Comment

    by:luttappi123

       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
    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
    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
    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
    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
    no rows selected.

    0
     
    LVL 1

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now