locked
Using a date string in SQL RRS feed

  • Question

  • User1732105960 posted

    I need to compare a date string along the lines of "10/2012" to a Date element in Oracle.  What would the SQL statement look like?  I need to compare both the month and the year (day doesn't matter).  Thanks!

    Friday, November 2, 2012 5:06 PM

Answers

  • User-2005691517 posted

    Yes, assuming processedDateDDL.SelectedValue is in the required format

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 6:07 PM
  • User-1407477457 posted

    So would this be the relevant portion of the SQL (assume the table/variable names are correct)

    WHERE to_char(PROCESSED_DATE, 'mm/yyyy') = processedDateDDL.SelectedValue

    That might give you the correct answer but using functions in the where clause tends to slow down production.  Your query would probably run a lot faster if you created some date variables based on that selected value and used this type of syntax.

    where processed_date >= first day of the month

    and processed_date < first day of following month

    This is especially relevent if processed_date is indexed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 6:35 PM

All replies

  • User-1716253493 posted
    convert(datetime,datestring)
    Friday, November 2, 2012 7:32 PM
  • User-1407477457 posted

    in oracle, the to_char() function will probably meet your needs.

    Friday, November 2, 2012 7:45 PM
  • User-2005691517 posted

    As Dan said, you can use to_char. You can refer to the documentation here http://psoug.org/reference/convert_func.html

    to_char(dateColumn, 'mm/yyyy') = '10/2012'

    Friday, November 2, 2012 8:56 PM
  • User1732105960 posted

    So would this be the relevant portion of the SQL (assume the table/variable names are correct)

    WHERE to_char(PROCESSED_DATE, 'mm/yyyy') = processedDateDDL.SelectedValue

    Monday, November 5, 2012 11:19 AM
  • User-2005691517 posted

    Yes, assuming processedDateDDL.SelectedValue is in the required format

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 6:07 PM
  • User-1407477457 posted

    So would this be the relevant portion of the SQL (assume the table/variable names are correct)

    WHERE to_char(PROCESSED_DATE, 'mm/yyyy') = processedDateDDL.SelectedValue

    That might give you the correct answer but using functions in the where clause tends to slow down production.  Your query would probably run a lot faster if you created some date variables based on that selected value and used this type of syntax.

    where processed_date >= first day of the month

    and processed_date < first day of following month

    This is especially relevent if processed_date is indexed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 6:35 PM