Date - String comparision

Shell Programming and Scripting
1

Hi,
I am having difficulty to compare a string in a file against a date from a a table and print the latest date. Below are the values.

String in File : 2009-12-02 00:37:51
Value Table : 2010-01-10-02.00.49.294758

I have to compare both the values ( Ignore the Microsecond in the table value) and select the most recent value.

Appreciate any help.

2

When you are extracting from table use to_char(date_fld,'YYYY-MM-DD HH:MI:SS') and store both the dates in same file

$ cat file
2009-12-02 00:37:51
2010-01-01 00:00:00
$ sort -r file | head -1
2010-01-01 00:00:00
3

Thanks for the reply.
The database is db2 and does not support to_char function.

4

Well, "to_char" is an Oracle built-in function; DB2 does support datetime formatting, although it's a bit convoluted:

select yyyy||'-'||mm||'-'||dd||' '||hh||':'||mi||':'||ss formatted_value
  from ( 
    select substr(digits(day(column_name)),9)    dd, 
           substr(digits(month(column_name)),9)  mm, 
           rtrim(char(year(column_name)))        yyyy, 
           substr(digits(hour(column_name)),9)   hh, 
           substr(digits(minute(column_name)),9) mi, 
           substr(digits(second(column_name)),9) ss  
    from table_name
);

You may want to create a UDF for this.

tyler_durden

5

Thanks.
I am trying to run the script the other way so that I can insert the values in the table. I need to change the value in the text file to a time stamp in db2.
So, basically I need the value in text file '2009-12-02 00:37:51' to be converted to '2009-12-02-00.37.51.000000'.

6

Use the TIMESTAMP() function for this conversion.

tyler_durden