# Date - String comparision

**URL:** <https://community.unix.com/t/date-string-comparision/264331>\
**Category:** Shell Programming and Scripting\
**Created:** [April 21, 2010, 4:09pm UTC](https://community.unix.com/t/date-string-comparision/264331 "2010-04-21T16:09:54Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sam\_78\_nyc](https://community.unix.com/letter_avatar/sam_78_nyc/32/6_22734026db12803ebb3e059622ac3534.png) [@sam\_78\_nyc](https://community.unix.com/u/sam_78_nyc)\
**Post date:** [April 21, 2010, 4:09pm UTC](https://community.unix.com/t/date-string-comparision/264331/1 "2010-04-21T16:09:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![anbu23](https://community.unix.com/user_avatar/community.unix.com/anbu23/32/351_2.png) [@anbu23](https://community.unix.com/u/anbu23)\
**Post date:** [April 21, 2010, 4:18pm UTC](https://community.unix.com/t/date-string-comparision/264331/2 "2010-04-21T16:18:52Z")

</div>

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

```nohighlight
$ 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

```

---

<div class="post-metadata">

**Author:** ![sam\_78\_nyc](https://community.unix.com/letter_avatar/sam_78_nyc/32/6_22734026db12803ebb3e059622ac3534.png) [@sam\_78\_nyc](https://community.unix.com/u/sam_78_nyc)\
**Post date:** [April 21, 2010, 9:16pm UTC](https://community.unix.com/t/date-string-comparision/264331/3 "2010-04-21T21:16:39Z")

</div>

Thanks for the reply.  
The database is db2 and does not support to\_char function.

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [April 21, 2010, 9:45pm UTC](https://community.unix.com/t/date-string-comparision/264331/4 "2010-04-21T21:45:41Z")

</div>

> [@sam\\\_78\_nyc](#):
>
> ...  
> The database is db2 and does not support to\_char function.

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

```plaintext
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

---

<div class="post-metadata">

**Author:** ![sam\_78\_nyc](https://community.unix.com/letter_avatar/sam_78_nyc/32/6_22734026db12803ebb3e059622ac3534.png) [@sam\_78\_nyc](https://community.unix.com/u/sam_78_nyc)\
**Post date:** [April 22, 2010, 2:10pm UTC](https://community.unix.com/t/date-string-comparision/264331/5 "2010-04-22T14:10:27Z")

</div>

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'.

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [April 22, 2010, 2:32pm UTC](https://community.unix.com/t/date-string-comparision/264331/6 "2010-04-22T14:32:46Z")

</div>

> [@sam\\\_78\_nyc](#):
>
> ...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'.

Use the TIMESTAMP() function for this conversion.

tyler\_durden