Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400. Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level. I'm not a UDB LUW expert, so don't take my word on that!
DB2 UDB for AS/400 is different!
DB2/400 is just a little bit different (and superior in this one particular case, though that is certainly not always the case!).
The good news is that in DB2/400, you can easily control the date format for any given connection to the database, and you can even change it from one format to a different one between subsequent queries in the same session.
The original question showed the output from this query:
Returns: 01/12/09, but the user wanted to see the date in mm/dd/ccyy format (01/01/2009).
First of all, understand that the SQL date() function is not dropping the century in the example above. The session that this query ran in is just configured for *MDY date format. It really has nothing to do with the DATE() function itself.
The century is still there, you just can't see it on screen or paper when the date format is set to *MDY, as it is apparently in the example query.
Bear with me and I'll explain what I mean.
DB2/400 DATE data type
The SQL DATE() function converts an expression representing a date (a string in …