Hi,

My problem is as follows :

On my database I have a view that is currently casting a date as char(10). The reason for this so that i can see the dates in SQL server (0001/01/01). Now what seems to be happening, is that on the cast, my date format is getting messed around, ie : from yyyy-mm-dd i am now only getting yy-mm-dd in the text field.

Using SQL server, i could overcome this problem by replacing the CAST with a CONVERT, but I don't know how to do this in DB2.

Any ideas?

In other words, how do i cast "0001/01/01" (date) as "0001/01/01" (char) ?
Currently after casting the yyyy becomes yy ...

use the function VARCHAR_FORMAT

Instead of CAST try using the CHAR function

CHAR(HIRE_DATE, ISO)

The ISO date is in format 'YYYY-MM-DD'

If you want all dates and times to appear in the ISO format without using the CHAR function, I believe that there is a DB2 instance setting to accomplish this.

Actually, I am not sure which format you wanted. Here are the DB2 formats:

ISO yyyy-mm-dd 1991-10-27
USA mm/dd/yyyy 10/27/1991
EUR dd.mm.yyyy 27.10.1991
JIS yyyy-mm-dd 1991-10-27

Thanks guys, worked brilliantly. Also saved me alot of headline headache.