How to convert Informix Dates?

[John Wolfe_1]

Hello Forum Members,

I am working with a large flat ASCII file that originally was exported from an Informix database application. I am able to deal with all the data except for three columns which are supposed to be dates but are actually integers. 1 is Jan. 1, 1900, 2 is Jan. 2, 1900 and so on. 36554 is actually Jan 30, 2000 in the original data. I need to somehow convert these values in yyyy/mm/dd format. I do not have access to Informix to do this so does anybody have a clue?

Any help will be greatly appreciated.

John W.

[Rodney Hills]

If you do a search on the forum for a process called "caljd.sh", it is a VERY handy script for date calculations. I can't remember who offered it, but it is THE tool in shell scripts to do date calculations.

If you take your informix date and add 2415020 to it and run-

caljd.sh number

It will return mm dd yyyy

-- Rod Hills

[A. Clay Stephenson · Accepted solution]

Hi John:

This is rather easy. It appears that in the Informix world December 31, 1899 was the dat the earth was created; i.e. day 0. We can take advantage of that and then use real Julian Days to tackle your problem. Julian Days count sequentially from 4713BCE so we just needd to know what the Julian Day of December 31, 1899 is and we are ready to roll. This should be close:

#!/usr/bin/sh

JD12311899=2415020
while [ ${#} -ge 1 ]
do
INFORMIX_DAY=${1}
shift
JD=$(( ${INFORMIX_DAY} + ${JD12311899} ))
DT=$(caljd.pl -y -S '/' ${JD})
echo "${DT}"
done
exit 0

You would use it like this:
my.sh 1 2 3
which would output
1900/01/01
1900/01/02
1900/01/03

You don't mention what language you are using but you could pull out the Jdate_Cal subroutine and use it in Perl or easily convert it to C - just make certain that all calculations use integer math. You can also find a Shell/Awk version 'caljd.sh' which uses exactly the same arguments as the attached Perl version, caljd.pl.

This should fix you, Clay

[John Wolfe_1]

Thanks guys. That was perfect. Clay, I just made one change to your sample shell script. I output everything on one line and sent all 3 of my Informix date values in at once. Your script even formatted the dates just like I wanted!!! yyyy/mm/dd

Here is my version :

JD12311899=2415020
while [ ${#} -ge 1 ]
do
INFORMIX_DAY=${1}
shift
JD=$(( ${INFORMIX_DAY} + ${JD12311899} ))
DT=$(caljd.pl -y -S '/' ${JD})
echo "${DT} \c"
done
echo
exit 0

Thanks again!

John W.

[A. Clay Stephenson]

Hi John:

I'm glad you liked it. You could have used my shell/awk based script ,caljd.sh, but since you are probably going to be calling the routine repeatedly, the Perl version, caljd.pl, will be much faster. I was very careful to make the command interface exactly the same between the two versions so that they are interchangable.

Regards, Clay

[John Wolfe_1]

Hi guys,

I have one more question. How did you know to use 2415020 as the value to add for 12/31/1899? There must be a program that that does the reverse calculation? jdcal.pl or jdcal.sh? I can't find it.

John W.

[A. Clay Stephenson]

Hi again John:

You are going to feel stupid; the program you are looking for is called 'caljd.pl (or caljd.sh)' - the one you already are using. If it sees 1 argument, it knows you want to convert Julian Days to calendar dates. If it sees 3 arguments, you must want the calendar date to Julian Day conversion. A special case is 0 arguments to process - that means take the current date and spit out the Julian Day equivalent. Specifically, in your case, all we needed to do was:
caljd.sh 12 31 1899 and 2415020 popped out.

Caljd.sh (or caljd.pl) are designed to be completely reversiblke so that often one call is nested within another. For example, suppose you want to find out the calendar date one week from the current date in dd-mm-yyyy format:

NEXTWK=$(caljd.pl -e -S '-' $(caljd.pl -n 7))
echo "Next Week = ${NEXTWK}"

The inner caljd.pl takes the current date (no args to process) and adds 7 to it (-n 7) and outputs a Julian Day. The outer caljd.pl then sees 1 argument (Must want a calendar date). The -e says format European Style dd mm yyyy and the -S '-' says replace the default separator (space) with '-'.

Caljd.pl (or caljd.sh) -u will give you a full usage message.

[John Wolfe_1]

Duh!!!

I guess I should have looked at the script. It says it all at the top. Rodney, you are right. This is THE date tool. I've now found both the caljd.sh version and the caljd.pl version and they work the same. The Perl version is (surprise) much faster.

Thanks again.

John W.