Change the date and time format in UNIX script.

Shell Programming and Scripting
1

Hi, I am extracting a date string from the source file like this :

06/05/2014 16:04:00

I want to change it to

  05-JUN-14 04.05.00.000000000 PM

I basically store the date in a variable. I got solutions to change date in dd-mmm-yyyy format using tr but I guess it works only with the "date" command and not with a variable that stores the old date.

The command is

 date '+%d-%h-%Y | tr 'a-z' 'A-Z'

But it is not working when I replace "date" with my variable 'tdate'.
Is there a way in UNIX to get the date and timestamp from a stored variable?

2

What have you tried so far?

Have you read the formatting options in the date manual page?

man date

What are you trying to achieve? If it's just set the alpha characters (month only) comes out as all capitals? If it's in a variable, you could:-

tdate="05-Jun-14 04.05.00.0000000000 PM"
typeset -u tdate
echo "$tdate"

Does that do it?

Robin

3

Are you adding a minute to get from 16:04:00 to 04.05.00.000000000 PM?
And, I don't think you can

. If you're lucky, you can use the date command as rbatte1 proposes, if not, you need to work on every single field...

4 
echo $tdate | tr 'a-z' 'A-Z'

BTW, You have unbalanced quotes in your original command. Highlighted in RED.

5

What I want to do is convert a date-time format

to

As I earlier said I cannot use the "date" command as this date is not the system date but a value that I am extracting from the input file like this :

tdat=`head -1 ${INPUTFILE} | cut -f9-10 -d'|' | sed 's/|/ /g' | cut -c1-19`

So basically I want to covert the format of this tdat variable.

What have I tried so far? I have used this variable and queried it in an oracle database which works fine, but I want to do this purely in UNIX.

---------- Post updated at 12:34 AM ---------- Previous update was at 12:33 AM ----------

What I want to do is convert a date-time format

to

As I earlier said I cannot use the "date" command as this date is not the system date but a value that I am extracting from the input file like this :

tdat=`head -1 ${INPUTFILE} | cut -f9-10 -d'|' | sed 's/|/ /g' | cut -c1-19`

So basically I want to covert the format of this tdat variable.

What have I tried so far? I have used this variable and queried it in an oracle database which works fine, but I want to do this purely in UNIX.

6

That's irrelevant if your date supports the -d --date option

date -d "06/05/2014 16:04:00" "+%d-%b-%y %I.%M.%S.%N %p"

FORMAT="+%d-%b-%y %I.%M.%S.%N %p"
date -d "$tdat" "$FORMAT"
7

:frowning: My system does not support -d :frowning:

8

If I understand what your input file looks like (working backwards from your code), the following awk script seems to do what you want:

#!/bin/ksh
INPUTFILE=${1:-data}
tdat=$(awk -F'|' '
BEGIN {	M[1] = "JAN"
	M[2] = "FEB"
	M[3] = "MAR"
	M[4] = "APR"
	M[5] = "MAY"
	M[6] = "JUN"
	M[7] = "JUL"
	M[8] = "AUG"
	M[9] = "SEP"
	M[10] = "OCT"
	M[11] = "NOV"
	M[12] = "DEC"
}
{	# Convert date in field 9 from "MM/DD/YYYY" to "DD-MMM-YY".
	d = substr($9, 4, 2) "-" M[substr($9, 1, 2) + 0] "-" substr($9, 9, 2)
	# Convert time in field 10 from "HH:MM:SS" to "hh.MM.SS.000000000 xM"
	H = substr($10, 1, 2)
	m = substr($10, 4, 2)
	S = substr($10, 7, 2)
	AP = H >= 12 ? "P" : "A"
	h = H + 0 > 12 ? H - 12 : H + 0 == 0 ? 12 : H
	printf("%s %02d.%s.%s.000000000 %sM\n", d, h, m, S, AP)
	exit
}' "$INPUTFILE"
)
printf "1st line from file \"%s\" is " "$INPUTFILE"
head -1 "$INPUTFILE"
printf "tdat has been set to \"%s\"\n" "$tdat"

I normally use the Korn shell, but this script will work with any shell that accepts basic POSIX shell requirements for command substitution and parameter expansions (such as bash and ksh ). If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

You can invoke this script with the name of your input data file (it defaults to using a file named data if you don't supply an operand). If you have a data file that contains:

f1|f2|f3|f4|f5|f6|f7|f8|06/05/2014|16:04:00junk|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|01/31/2013|00:01:02|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|02/29/2012|03:04:05|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|03/30/2011|06:07:08|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|04/28/2010|09:10:11|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|05/27/2010|12:13:14|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|06/26/2009|15:16:17|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|07/25/2008|18:19:20|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|08/24/2007|21:22:23|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|09/23/2006|01:02:03|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|10/22/2005|04:05:06|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|11/21/2004|07:08:09|f11|f12
f1|f2|f3|f4|f5|f6|f7|f8|12/20/2003|10:11:12|f11|f12

it produces the output:

1st line from file "data" is f1|f2|f3|f4|f5|f6|f7|f8|06/05/2014|16:04:00junk|f11|f12
tdat has been set to "05-JUN-14 04.04.00.000000000 PM"

To verify that the month translations are all working correctly, if you remove (or comment out) the exit in the awk script, it will produce the output:

tdat has been set to "05-JUN-14 04.04.00.000000000 PM
31-JAN-13 12.01.02.000000000 AM
29-FEB-12 03.04.05.000000000 AM
30-MAR-11 06.07.08.000000000 AM
28-APR-10 09.10.11.000000000 AM
27-MAY-10 12.13.14.000000000 PM
26-JUN-09 03.16.17.000000000 PM
25-JUL-08 06.19.20.000000000 PM
24-AUG-07 09.22.23.000000000 PM
23-SEP-06 01.02.03.000000000 AM
22-OCT-05 04.05.06.000000000 AM
21-NOV-04 07.08.09.000000000 AM
20-DEC-03 10.11.12.000000000 AM"
9

Thank You SOOOOO much Don!! You have saved my life!! Thank you very very much! It is working perfectly well!:slight_smile: