locked
How to add the current date in the query RRS feed

  • Question

  • User-1506965535 posted

    I have a query where I want the data for current data.

    Here is my below query

    string query = "SELECT Distinct T1.Project_Id,PROPERTY_NAME Project_name,T1.Vehicle_No,T1.Creation_date,T1.Time_In,T1.Time_Out FROM XXCUS.XX_SUPINV T1 INNER JOIN XXACL_PN_PROJBUILD_V T2 ON T1.Project_Id = T2.Project_id WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' AND Creation_date BETWEEN fnd_conc_date.string_to_date('" + TxtIndate.Value + "') AND fnd_conc_date.string_to_date('" + txtOutDate.Value + "')";

    How to add Current date in place of "TxtIndate.Value" and "TxtOutdate.Value"

    Saturday, July 4, 2015 7:34 AM

Answers

  • User475983607 posted

    I have a query where I want the data for current data.

    Here is my below query

    string query = "SELECT Distinct T1.Project_Id,PROPERTY_NAME Project_name,T1.Vehicle_No,T1.Creation_date,T1.Time_In,T1.Time_Out FROM XXCUS.XX_SUPINV T1 INNER JOIN XXACL_PN_PROJBUILD_V T2 ON T1.Project_Id = T2.Project_id WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' AND Creation_date BETWEEN fnd_conc_date.string_to_date('" + TxtIndate.Value + "') AND fnd_conc_date.string_to_date('" + txtOutDate.Value + "')";

    How to add Current date in place of "TxtIndate.Value" and "TxtOutdate.Value"

    From you're other post, you are using Oracle.  It has been a while but I believe you must format the date correctly so Oracle can implicitly cast the string to a date.

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00210

    Again, you really should use parameters.  It will solve the problems you are creating using string concatenation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 4, 2015 8:06 AM

All replies

  • User2024324573 posted

    How to add Current date in place of "TxtIndate.Value" and "TxtOutdate.Value"

    Replace them with Date();

    Hope this will help.

    Saturday, July 4, 2015 7:50 AM
  • User475983607 posted

    I have a query where I want the data for current data.

    Here is my below query

    string query = "SELECT Distinct T1.Project_Id,PROPERTY_NAME Project_name,T1.Vehicle_No,T1.Creation_date,T1.Time_In,T1.Time_Out FROM XXCUS.XX_SUPINV T1 INNER JOIN XXACL_PN_PROJBUILD_V T2 ON T1.Project_Id = T2.Project_id WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' AND Creation_date BETWEEN fnd_conc_date.string_to_date('" + TxtIndate.Value + "') AND fnd_conc_date.string_to_date('" + txtOutDate.Value + "')";

    How to add Current date in place of "TxtIndate.Value" and "TxtOutdate.Value"

    From you're other post, you are using Oracle.  It has been a while but I believe you must format the date correctly so Oracle can implicitly cast the string to a date.

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00210

    Again, you really should use parameters.  It will solve the problems you are creating using string concatenation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 4, 2015 8:06 AM
  • User61956409 posted

    Hi Nadeem,

    You could try to use TO_DATE function to convert a string to a date, those links discussed about selecting records based on date.

    http://stackoverflow.com/questions/11039830/how-to-filter-based-on-the-date-in-oracle-sql

    http://stackoverflow.com/questions/11461099/how-to-convert-datestring-type-into-datedate-time-in-oracle

    Best Regards,

    Fei Han

    Monday, July 6, 2015 3:26 AM