I have a variable in data/time format and I want to replace it with the first parameter of below string in a file with the user input variable

TO_DATE('07/13/2021 00:30:00' 'MM/DD/YYYY HH24:MI:SS')

I want to replace '07/13/2021 00:30:00' with my variable.

I have a script which would take user input for start time and store it in a variable 'st'

I have a sql file which a static query. I need help in replacing TO_DATE('07/13/2021 00:30:00' with the variable and then the script would execute.

Tried below sed command and it's not working

sed -e 's/(.TO_DATE()[^ ]( .*)/\1'$st'\2/' /apps/tmp/abc.sql

Getting error message: Unknown option to `s' I am new to sed stuff. I am new to sed stuff

Can someone please help?

  • What have you tried? Please edit your question and include your attempts, and how they failed.
    – Panki
    Jul 14, 2021 at 14:26
  • This feels like you're opening up the possibility of sql injection. Jul 14, 2021 at 14:45
  • Edited my post. Jul 14, 2021 at 15:01

1 Answer 1


With sed, you'll want the s/// command, but beware that the the replacement text contails slashes. You can use different delimiters for the s command: here I'll use commas:

sed -E "s,(TO_DATE\(')[^']+,\1${timestamp}," file.sql


$ cat file.sql
select * from table1
where created > TO_DATE('07/13/2021 00:30:00', 'MM/DD/YYYY HH24:MI:SS');

$ timestamp=$(date '+%m/%d/%Y %T')

$ sed -E "s,(TO_DATE\(')[^']+,\1${timestamp}," file.sql
select * from table1
where created > TO_DATE('07/14/2021 10:46:51', 'MM/DD/YYYY HH24:MI:SS');

The keys here are to use double quotes for the sed body to allow the shell to expand the variable, and to choose different delimiters for s///

And as commented, thoroughly validate the contents of the variable to keep out Little Bobby Tables

  • Thank you very very much Glenn. It worked for me. Appreciate your quick help!! Jul 14, 2021 at 15:15
  • Hi Jhalak, if glenn's answer worked for you, maybe you should mark his answer as accepted? May 31 at 9:43
  • Thanks for the backup, but a user with 1 rep and 1 question from 2021 is unlikely to see your comment. May 31 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .