Re: find and replace yesterday's with todays date in sql file (161 Views)
Reply
Frequent Advisor
diwakar_4
Posts: 40
Registered: ‎07-14-2008
Message 1 of 8 (161 Views)

find and replace yesterday's with todays date in sql file

Hi All,

I have sql file which contains one confition like:

OR ((n_works_notices.local_rec_date = "2008-11-11")

Everyday this date is replqced with today's date. For that i am using sed command:

sed -e 's/((n_works_notices.local_rec_date = ............/((n_works_notices.l
ocal_rec_date = "2008-11-11"/1' mysql.sql > temp.sql

Now i have new sql file which contains condition as

AND ("2008-11-08">=from_date) AND ("2008-11-08"<=to_date)

Now how to replace these to dates(2008-11-08) with todays date.
Everyday i have to find existing date and replace with today's date.

Can some one please help me?

Thanks
Honored Contributor
Suraj K Sankari
Posts: 1,659
Registered: ‎09-09-2008
Message 2 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

Hi,

You can store current date into a variable and use this variable where you need current date see the below example:

$ dt=`date +%Y"-"%d"-"%m`
$ echo $dt
2008-11-11


Suraj
Frequent Advisor
diwakar_4
Posts: 40
Registered: ‎07-14-2008
Message 3 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

Hi Suraj,

I am doing same. But can you please show the complete sed statement.
Acclaimed Contributor
Dennis Handly
Posts: 25,277
Registered: ‎03-06-2006
Message 4 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

>can you please show the complete sed statement.

The problem is how to compute yesterday.

The simplest way is to start with a template file where you don't have yesterday but you have a template: YYYY-MM-DD

Then you just have a sed command that changes that to today:
$ dt=$(date +%Y-%d-%m)
$ sed "s/YYYY-MM-DD/$dt/g" template.sql > temp.sql
Honored Contributor
OldSchool
Posts: 3,372
Registered: ‎09-09-2004
Message 5 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

if "from_date" and "to_date" are "date" fields and not "character" or "var_char", then depending on *whose* sql this is, the "date" functions should be capable of handling this directly.

Look for something like "date('today')" in your implementation. In addition, you might need to fiddle the date format

Frequent Advisor
diwakar_4
Posts: 40
Registered: ‎07-14-2008
Message 6 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

Hi dennis,

I was just thinking of idea to find pattern like YYYY-MM-DD in sql and replacing with current date.
Can you sugget how to write pattern and dates following this pattern will be getting changed.

It means it if it fileds date in statement :
OR ((n_works_notices.local_rec_date = "2008-11-11")

it will update to current date.

Thanks
Acclaimed Contributor
Dennis Handly
Posts: 25,277
Registered: ‎03-06-2006
Message 7 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

>I was just thinking of idea to find pattern like YYYY-MM-DD in sql

I'm suggesting you use EXACTLY that string and change it every time you need it.

I suppose you could look for DDDD-DD-DD (D is a digit) but why bother?
Frequent Advisor
diwakar_4
Posts: 40
Registered: ‎07-14-2008
Message 8 of 8 (161 Views)

Re: find and replace yesterday's with todays date in sql file

Hi Dannis,

Thanks a lot.

Looks it will help.

Thanks
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.