Oracle query concatenate date

Question

I have the following query: ``` select * from mytable where to_char(mydate,'mm/dd/yyyy') between ('05/23/2013') and ('06/22/2013') ``` I need to change it to make dynamically so that I won't modify it every month from `05/23/2013` to `06/23/2013` for example: ``` ('05/23/' + (select to_char(sysdate, 'yyyy') from dual)) ``` but this is giving an error. Any suggestions? What I need to do: Every month I need to run this query to get the records between 23rd of this month and 23rd of the last month.

Accepted Answer

Oracle uses `||` as the concatenation operator:

```
('05/23/' || (select to_char(sysdate, 'yyyy') from dual))
```

BTW, David is right. If you really want to compare string representations of dates (but why?), use a date format that is ordered the same way as dates:

```
to_char(mydate,'yyyy/mm/dd')
```

Oracle query concatenate date

Solution

Problem