How to convert date text string to new format for use in query. 2

[hallux]

Hello,
I need to keep the date numbers and replace instances of
[blue]{ts '2004-06-01 00:00:00.00'}[/blue] with a string
[green]to_date('2004-06-01','YYYY-MM-DD')[/green] to use in database queries.

Anybody have any cool one-liners to do the job?

Just for fun, any guesses as to the source of the first date string and the database I'll be using the new string for the query.

Thanks,
-Hallux

 

[Ygor]

Put this in a file named repl.sed (or whatever) ...
[tt]
s/{ts '\(....-..-..\) ..:..:.....'}/to_date('\1','YYYY-MM-DD')/g
[/tt]
...use it like this...
[tt]
sed -f repl.sed infile > outfile
[/tt]
I guess you're converting to Oracle, but I don't know what from?

 

[hallux]

Thanks Ygor,
Works perfectly! Thanks for the details on building the sed file and the syntax. Since I pull these all the time, I am creating a ksh script to wrap it all together.

Question: In the replace section I see that the date string '2004-06-01' is held by a variable \1. How does the code read?: is it that everything after the first backslash up to the second backslash is in the variable 1?

Regarding the guesses.
Yes, it is converted to Oracle format. A clue for the source date is that it is output from a popular reporting tool.

-Hallux

 

[CaKiwi]

or using awk

awk -f hallux.awk input > output

#hallux.awk
{print "to_date(" $2 "','YYYY-MM-DD')"}

CaKiwi

 

[hallux]

Thanks CaKiwi,
I appreciate the awk code. For my situation I'll utilize Ygor's sed code because the date string can be situated anywhere in the file and may not always be in column 2.
I gave everybody a star. Thanks all!

FYI: The source of the string {ts '2004-06-01 00:00:00.00'} is Crystal Reports 8.5.

 

[Ygor]

The \1 matches the first subexpression enclosed between \( and \)