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!
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.
Kovarian
Member
2 Points
10 Posts
Using a date string in SQL
Nov 02, 2012 09:06 PM|LINK
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!
oned_gk
All-Star
31511 Points
6431 Posts
Re: Using a date string in SQL
Nov 02, 2012 11:32 PM|LINK
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Using a date string in SQL
Nov 02, 2012 11:45 PM|LINK
in oracle, the to_char() function will probably meet your needs.
Prashant Kum...
Star
12334 Points
1992 Posts
Re: Using a date string in SQL
Nov 03, 2012 12:56 AM|LINK
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'
Kovarian
Member
2 Points
10 Posts
Re: Using a date string in SQL
Nov 05, 2012 03:19 PM|LINK
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
Prashant Kum...
Star
12334 Points
1992 Posts
Re: Using a date string in SQL
Nov 06, 2012 10:07 PM|LINK
Yes, assuming processedDateDDL.SelectedValue is in the required format
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Using a date string in SQL
Nov 06, 2012 10:35 PM|LINK
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.