Displaying date in DD MM YY format

Hi, I am looking at sysdbstab for level0. The value is stored as integer. How do I display this value as normal date DDMMYYYY in an SQL statement, Like: select level0 from sysdbstab; With the best regards and thanks, Saradhi Motamarri +61430022130 Sent from Yahoo Mail for iPhone #Informix

Hi Saradhi, like this? See the link below for a description of the to_char function. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1542.htm [informix@db2 skjeong]$ dbaccess sysmaster - Database selected. > select to_char(dbinfo('utc_to_datetime',level0), '%d%m%Y')::char(8) date from sysdbstab; date 03032020 03032020 03032020 03032020 03032020 03032020 03032020 03032020 03032020 03032020 03032020 03032020 12 row(s) retrieved. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Saradhi: This way: > select dbinfo( 'utc_to_datetime', level0) from sysdbstab; (expression) 2019-11-27 20:15:38 2019-11-27 20:15:38 2019-11-27 20:15:38 2019-11-27 20:15:38 2019-11-27 20:15:38 2019-11-27 20:15:38 Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

You can use the DBINFO function with utc_to_datetime option https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1495.htm example : > !onstat -g arc IBM Informix Dynamic Server Version 12.10.FC9 -- On-Line (Prim) -- Up 02:22:38 -- 1395428 Kbytesnum DBSpace Q Size Q Len Buffer partnum size scanner Dbspaces - Archive Status name number level date log log-position rootdbs 1 0 03/29/2019.11:42 45 0x3b94d8 sbspace 2 0 03/29/2019.11:42 45 0x3b94d8 sbspace_000 3 0 03/29/2019.11:42 45 0x3b94d8 dbs1 4 0 03/29/2019.11:42 45 0x3b94d8 dbs2 5 0 03/29/2019.11:42 45 0x3b94d8 > select name, dbinfo("utc_to_datetime",level0) from sysdbstab;name rootdbs (expression) 2019-03-29 11:42:27 name sbspace (expression) 2019-03-29 11:42:27 name sbspace_000 (expression) 2019-03-29 11:42:27 name dbs1 (expression) 2019-03-29 11:42:27 name dbs2 (expression) 2019-03-29 11:42:27 name temp1 (expression) 1970-01-01 10:00:00 name temp2 (expression) 1970-01-01 10:00:00 7 row(s) retrieved. ------------------------------ CASEY TAN ------------------------------

Using DBINFO function with utc_to_datetime option. If you require in DDMMYYYY format, use to_char(). Example : > select name, dbinfo("utc_to_datetime",level0) level0 from sysdbstab;name rootdbs level0 2019-03-29 11:42:27 name sbspace level0 2019-03-29 11:42:27 name sbspace_000 level0 2019-03-29 11:42:27 name dbs1 level0 2019-03-29 11:42:27 name dbs2 level0 2019-03-29 11:42:27 name temp1 level0 1970-01-01 10:00:00 name temp2 level0 1970-01-01 10:00:00 7 row(s) retrieved. > !onstat -g arc IBM Informix Dynamic Server Version 12.10.FC9 -- On-Line (Prim) -- Up 02:35:03 -- 1395428 Kbytesnum DBSpace Q Size Q Len Buffer partnum size scanner Dbspaces - Archive Status name number level date log log-position rootdbs 1 0 03/29/2019.11:42 45 0x3b94d8 sbspace 2 0 03/29/2019.11:42 45 0x3b94d8 sbspace_000 3 0 03/29/2019.11:42 45 0x3b94d8 dbs1 4 0 03/29/2019.11:42 45 0x3b94d8 dbs2 5 0 03/29/2019.11:42 45 0x3b94d8 With to_char added. > select to_char(dbinfo("utc_to_datetime",level0),'%d%m%Y') from sysdbstab; (expression) 29032019 (expression) 29032019 (expression) 29032019 (expression) 29032019 (expression) 29032019 (expression) 01011970 (expression) 01011970 7 row(s) retrieved. ------------------------------ CASEYTAN ------------------------------

Use DBInfo for converting UTD date to real Informix datetime: DBINFO ('utc_to_datetime', integer_column ) Regards,Vicente Salvador ------------------------------ Vicente Salvador ------------------------------

Thank you Art and SangGyu.