Informix

 View Only
  • 1.  Displaying date in DD MM YY format

    Posted Thu March 05, 2020 11:24 PM
    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


  • 2.  RE: Displaying date in DD MM YY format

    Posted Fri March 06, 2020 12:51 AM
    Edited by System Fri January 20, 2023 04:34 PM
    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
    ------------------------------



  • 3.  RE: Displaying date in DD MM YY format

    IBM Champion
    Posted Fri March 06, 2020 06:19 AM
    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


    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.








  • 4.  RE: Displaying date in DD MM YY format

    Posted Sat March 07, 2020 07:14 PM
    Thank you Art and SangGyu.





  • 5.  RE: Displaying date in DD MM YY format

    Posted Fri March 06, 2020 08:38 AM
    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 Kbytes

    num 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
    ------------------------------



  • 6.  RE: Displaying date in DD MM YY format

    Posted Fri March 06, 2020 08:38 AM
    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 Kbytes


    num 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
    ------------------------------



  • 7.  RE: Displaying date in DD MM YY format

    Posted Fri March 06, 2020 08:42 AM
    Use DBInfo for converting UTD date to real Informix datetime:

    DBINFO ('utc_to_datetime', integer_column )

    Regards,
    Vicente Salvador


    ------------------------------
    Vicente Salvador
    ------------------------------