DATE TIME formatting

can anyone one help me....to make date and time format...to following format for my file

Code:

DATE                           TIME DD- MON- YEAR         24 Hours

I have a need of format like this

12-Jan-2012 in one column, then time in 24 Hours in another column....please help...me...

currently we are upgrading our database....kindly help to make standard format...

our database containing format like this

Code:

DD-MON-YEAR MON-DAY-YEAR YEAR-MON-DAY DD/MON/YEAR MON/DAY/YEAR YEAR/MON/DAY

some files containing month name, some number...

and time format
both 12 and 24 hours are mixed

script has to read current format of date and time and then write to new file with standard format

Awaiting for help...

here is my data

DATE,PR,ST,ONC,NOD
7/19/1979 18:00,748,1,43,1
7/19/1979 18:00,748,1,43,1
7/19/1979 18:00,748,1,43,1
7/19/1979 18:00,748,1,43,1
7/19/1979 18:00,748,1,43,1
7/19/1979 18:00,748,1,43,1
7/19/1979 06:00:00 AM,748,1,43,1
25/1/1979 06:00:00 AM,748,1,43,1
20/1/1979 06:00:00 AM,748,1,43,1
31/9/1979 06:00:00 AM,748,1,43,1
20/1/1979 06:00:00 AM,748,1,43,1
31/9/1979 06:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,748,1,43,1
25-Jul-1979 03:00:00 AM,749,2,44,2
25-Jul-1979 03:00:00 AM,750,3,45,3
Feb-08-1988 12:05:00 PM,751,4,46,4
Feb-08-1988 12:05:00 PM,752,5,47,5
08-Feb-1988 18:00,753,6,48,6
08-Feb-1988 18:00,754,7,49,7
25-1-1979 06:00:00 AM,748,1,43,1
20-1-1979 06:00:00 AM,748,1,43,1
1-15-1979 06:00:00 AM,748,1,43,1
2-19-1979 06:00:00 AM,748,1,43,1

those who know please do favour for me...

Maybe mentionning your OS and version would help you know...

Becasue some OS verion have special functionality in the date command.

Ubuntu 12.04 LTS 64 bit. I am using, database is in windows xp os, I have planned extract data from windows pc, and then modify date and time format as I mentioned

To show what Jim ( and I ) were taliking about :

onas:/home/vbe $ date --d="25-Jul-1979 03:00:00 AM"  # Extracted from your data, just for fun...
Wed Jul 25 03:00:00 CET 1979
onas:/home/vbe $ date --d="25-Jul-1979 03:00:00 PM"   # and modified to PM to se...
Wed Jul 25 15:00:00 CET 1979
onas:/home/vbe $ uname -r
2.6.32.24
onas:/home/vbe $ 

This system is an debian I think...

So I would start by reading the man pages of date on your system to see what it can do...

try something like this..as your format is changing so handle the different condition...for thounsand or records you can redirect the failing records to error file...below is the same code for your reference ....date --d="$i" +%d-%b-%Y

/bea/mani >cat dateformat.sh
IFS="
"
for i in `cat problem.txt| awk -F"," '{print $1}'`
do
echo $i;
if [[ "$i" == */* ]]
then
echo "String contains /"
dt=`echo $i | awk -F"\/" '{print $1}'`
if [ $dt -gt 12 ]
then
echo "Not a month"
mn=`echo $i | awk -F"\/" '{print $2}'`
i=`echo $i|sed "s/"${dt}\/${mn}"/"${mn}\/${dt}"/"`
echo $i
fi
fi
if [[ "$i" == *-* ]]
then
echo "Contains -"
dt=`echo $i | awk -F"\/" '{print $1}'`
if [ $dt -gt 12 ]
then
echo "Not a month"
mn=`echo $i | awk -F"\/" '{print $2}'`
i=`echo $i|sed "s/"${dt}\/${mn}"/"${mn}\/${dt}"/"`
echo $i
fi
fi
date --d="$i" +%d-%b-%Y
read;
done
/bea/mani >

This is not only not easy, but some records will be impossible to translate, be it manually or by program logic - those using two numbers below 13 for day and month in an undefined order. These will be printed to an error file. For the rest, and if your awk versions allows for it, try:

$ awk  'BEGIN {split("Jan Feb Mar Apr May Jun Jul AUg Sep Oct Nov Dec", months)}
         NR==1 {print;next}
         {n=split ($1,Dt,"[-/ :]")                                            # split all date/time info into up to 7 array elements
          if (!(Dt[1]+0))                     {Mon=Dt[1]; Day=Dt[2]}          # check if there's an alpha month's name; use it, the other element is the day
            else if (!(Dt[2]+0))              {Mon=Dt[2]; Day=Dt[1]}
                   else if (Dt[1]>12)         {Mon=months[Dt[2]]; Day=Dt[1]}  # check if there's one element greater than 12 - must be a day; use it, the other is a month
                          else if (Dt[2]>12)  {Mon=months[Dt[1]]; Day=Dt[2]}
                                 else         {print > "errorfile";next}      # all other records cannot be correctly translated

          if (Dt[7]=="PM") Dt[4]+=12                                          # handling PM may need to be refined
         
          print Day"-"Mon"-"Dt[3], Dt[4]":"Dt[5]":" sprintf ("%02d",Dt[6]+0), $2, $3, $4,$5
         }
         
        ' FS="," file
DATE,PR,ST,ONC,NOD
19-Jul-1979 18:00:00 748 1 43 1
25-Jan-1979 06:00:00 748 1 43 1
20-Jan-1979 06:00:00 748 1 43 1
31-Sep-1979 06:00:00 748 1 43 1
20-Jan-1979 06:00:00 748 1 43 1
31-Sep-1979 06:00:00 748 1 43 1
25-Jul-1979 03:00:00 748 1 43 1

The AM/PM logics may need some refinement, as does the output of the time fields in general. Regard this as an exercise for yourself...

Thank you so much RudiC you are always rocking