Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have an SQL fact table which is used for looking up a date and returning another date.

The table schema is as follows:

TABLE date_lookup
   (
pk_date DATE,
plus1_months DATE,
plus2_months DATE,
plus3_months DATE
   );

UNIQUE INDEX on date_lookup(pk_date);

I have a load file (pipe delimited) containing dates from 01-28-2012 to 03-31-2014.

The following is an example of the load file:

01-28-2012|02-28-2012|03-28-2012|04-28-2012|

01-29-2012|02-29-2012|03-29-2012|04-29-2012|

01-30-2012|02-29-2012|03-30-2012|04-30-2012|

01-31-2012|02-29-2012|03-31-2012|04-30-2012|

...

03-31-2014|04-30-2014|05-31-2014|06-30-2014|

The rules for this fact table are: If pk_date has more than 28 days in its month and plus1, plus2 or plus3_months only has 28, 29 or 30 days, then let plus1, plus2 or plus3 equal the last day of the following month.

My question is: Is it possible to use sed to generate more dates past 03-31-2014 following the above rules for my load file, or should I write a program to accomplish this?

share|improve this question
1  
most sqls have a dateadd(), datediff() and similar functions that make this very easy to compute. It may be possible to do this in sed, but would be torture to create and a fireable offence at most organization to check it in as code that someone else may have to maintain ;-)! Good luck. –  shellter Jul 16 '12 at 4:35
add comment

2 Answers

I doubt that sed is the right tool for the job, in this case. I think you probably want to use awk, if you're already familiar with awk, otherwise, write a program.

I've known an engineer who used sed and awk to create MSC/NASTRAN input files, which had even stricter requirements than what you mention, but he was quite familiar with the tools, so cryptic and uninformative error messages from awk didn't bother him.

If you want to do this with awk, my advice would be to pre-process the input to remove '-' and '|' characters. Write the awk program in a separate file. Use text-field positions ($1, $2, $3...), and write SQL INSERT statements for the output. It will take several attempts, so automate as much as possible.

share|improve this answer
add comment

While it is technically possible to do date arithmetic in sed, it is not at all the right tool for the job. Use a tool like awk or perl which has integer arithmetic built in.

Your requirement is an unusual one for date manipulations, so you'll need a rich date manipulation library if you don't want to hard-code the date arithmetic. Perl's Date::Manip has functions like Date_DaysInMonth.

#!/usr/bin/env perl
use strict;
use warnings;
use Date::Manip;
use List::Util qw(/./);
sub shift_month {
    my ($delta, $y, $m, $d) = @_;
    $m += $delta;
    $y += $m / 12;
    $m %= 12;
    return ($y, $m, min($d, Date_DaysInMonth($m, $y)));
}
my $next_day = ParseDateDelta("1 day");
my ($cur, $end) = (ParseDate($ARGV[0]), ParseDate($ARGV[1]));
while (Date_Cmp($cur, $end) < 0) {
    my @ymd = UnixDate($cur, qw(%Y %m %d));
    foreach my $i (0..3) {
        printf "%02d-%04d-%02d|", @{[shift_month($i, @ymd)]}[1, 0, 2];
    }
    $cur = DateCalc($cur, $next_day);
    print "\n";
}
share|improve this answer
    
I found out that the best solution is to do it with Informix SQL's date() function. see answer here –  FrankComputerAtYmailDotCom Jul 17 '12 at 23:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.