Fetch data from date to date

Question

I am new in Oracle and C# program. Now I am trying to make a coding program for fetching data from Oracle database using datetimepicker from date to date, and I want to show it in datagridview using C#. But I have got trouble when I tried it, I got error info like this {"ORA-01843: not a valid month"} . I have tried to solve this problem with many ways, but I did not find it. Does anyone here know how to fix this problem ? Here's my coding program

OracleDataAdapter sda = new OracleDataAdapter(@"SELECT
  A.LINE_CD
, A.ITM_CD
, B.ITM_NM
, A.ACT_CAVITY AS CAVITY
, A.INST_NO AS MFG_NO
, A.PROD_SCHD_QTY AS SCHD_QTY
, A.PROD_ST_SCHD_DT AS START_SCHD
, A.PROD_END_SCHD_DT AS END_SCHD
, A.SHIFT
, TO_CHAR(A.PROD_DT,'DD/MM/YYYY') AS PROD_DT
, A.PROD_RESULT
, A.TF_FG
, SUM(A.PROD_RESULT) AS TOTAL_PROD
, SUM(A.TF_FG) AS TOTAL_TRANSFER
, SUM(A.PROD_RESULT - A.TF_FG) AS LOSS_TRANSFER
FROM TMCI_IM_PROD A 
  INNER JOIN CM_HINMO_ALL B 
    ON A.ITM_CD=B.ITM_CD 
WHERE 
     A.PROD_RESULT <= A.PROD_SCHD_QTY AND
     A.PROD_ST_SCHD_DT BETWEEN '" + txtPRODSTSCHDDT.Value.ToString() + "'
      AND '" + txtPRODSTSCHDDT2.Value.ToString() + "' 
GROUP BY
  A.LINE_CD
, A.ITM_CD
, B.ITM_NM
, A.ACT_CAVITY
, A.INST_NO
, A.PROD_SCHD_QTY
, A.PROD_ST_SCHD_DT
, A.PROD_END_SCHD_DT
, A.SHIFT
, A.PROD_DT
, A.PROD_RESULT
, A.TF_FG 
ORDER BY A.PROD_ST_SCHD_DT DESC", con);                                                   
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;

Don't build queries using string concatenation - use bind variables and pass your dates in as dates not strings and then you know they are valid dates and you are not vulnerable to SQL injection attacks. — MT0, Oct 25 '18 at 08:20

score 1 · Answer 1 · answered Oct 25 '18 at 03:49

1

You can use oracle TO_DATE function combined with ToString()with specified custom format e.g.

A.PROD_ST_SCHD_DT BETWEEN TO_DATE("+ txtPRODSTSCHDDT.Value.ToString("dd/MM/yyyy")+", 'DD/MM/YYYY') ...

One aditional comment: it is good to consider using parametrized queries which prevents sql injection and having better performance.

answered Oct 25 '18 at 03:49

rychlmoj

385
1
3
14

It doesn't work, I've got this error information {"ORA-00907: missing right parenthesis"} – nurman syah Oct 25 '18 at 04:08
Don't build queries using string concatenation - use bind variables and pass your dates in as dates not strings and then you know they are valid dates and you are not vulnerable to SQL injection attacks. – MT0 Oct 25 '18 at 08:28

score 0 · Answer 2 · answered Oct 25 '18 at 03:38

0

Have you checked if you are getting valid date values from txtPRODSTSCHDDT.Value.ToString() and txtPRODSTSCHDDT2.Value.ToString()?

answered Oct 25 '18 at 03:38

paqpojke

26
2

Yes, I have checked it. And If it I change the date value and execute it with using this code A.PROD_ST_SCHD_DT BETWEEN TO_DATE('22-Oct-2018', 'DD-Mon-YYYY') AND TO_DATE('24-Oct-2018', 'DD-Mon-YYYY') the program will be Ok – nurman syah Oct 25 '18 at 03:47
I found it, now the problem has been fixed. Thank you for your valuable advice – nurman syah Oct 25 '18 at 06:38

score 0 · Answer 3 · answered Oct 25 '18 at 04:00

0

Unless you've changed it, Oracle's default date format is DD-MMM-YYYY (e.g. 24-Oct-2018). Your PL/SQL is written to expect the textbox to have the date in this format. It would be unusual for an end user to have that date format in a user interface, so it's likely that your textbox has a different date format in it, such as 10/24/2018. This will fail because Oracle doesn't know how to interpret the date from your user interface.

You need to standardize the input textbox date format in your code before passing it into the Oracle query.

answered Oct 25 '18 at 04:00

Joel Brown

14,123
4
52
64

Thank you in advance. Actually It's not textbox but it's datetimepicker – nurman syah Oct 25 '18 at 04:08
@nurmansyah A date time picker is better than a textbox because it's got some data formatting built into it. However, as paqpojke pointed out, it's quite possible that the format being output by (control).value.ToString() is not the DD-MMM-YYYY format that Oracle expects, even if your UI is showing that format. This is because you're flowing it through the .value. – Joel Brown Oct 25 '18 at 04:19
I found it, now the problem has been fixed. Thank you for your valuable advice – nurman syah Oct 25 '18 at 06:38
[Oracle's default date format](https://stackoverflow.com/a/50164234/1509264) depends on the territory set when you create the database. If your territory is Israel then you have the default `DD-MON-RRRR` but defaults for other territories are different (and none of them are `DD-MON-YYYY`). Also, if you use bind parameters and pass the values in as a date rather than using string concatenation to build your query then you do not need to format the date and you avoid issues with SQL injection attacks. – MT0 Oct 25 '18 at 08:32

Fetch data from date to date

3 Answers3