CA Client Automation

Expand all | Collapse all

Hello everybody, I'm trying to understand the Date field in the database, but i need to know when a User account was created, but this field only contains numbers(Integer), and I need to know how can I do if I need datetime value like 'YYYY/MM/DD'

Jump to Best Answer

carvato01-12-2018 07:51 AM

  • 1.  Hello everybody, I'm trying to understand the Date field in the database, but i need to know when a User account was created, but this field only contains numbers(Integer), and I need to know how can I do if I need datetime value like 'YYYY/MM/DD'

    Posted 01-12-2018 07:51 AM

    #Date #Help 



  • 2.  Re: Hello everybody, I'm trying to understand the Date field in the database, but i need to know when a User account was created, but this field only contains numbers(Integer), and I need to know how can I do if I need datetime value like 'YYYY/MM/DD'
    Best Answer

    Posted 01-12-2018 10:08 AM

    All timestamp fields are stored as an Integer type, which is referred to as "Epoch" time.  It is literally the number of seconds counted since 1-Jan 1970.

     

    You can always reference this converter:

    Epoch Converter - Unix Timestamp Converter 

     

    To convert the value in SQL:

    select dateadd(s, <column name>, '19700101')

     

    Where <column name> is the column that contains the integer date.

     

    NOTE: All time stamps are stored in GMT, and are not localized.  This is done to standarize all time stamp fields stored in the database, as it's your Enterprise, Domains, Scalability servers and Agents could all practically be in different time zones.

     

    So if you want to get a bit more fancy, you can do a calculation based on the current time, in order to dynamically adjust the time stamp to the local time:

    select dateadd(s, datediff(s, getutcdate(), getdate()), dateadd(s, <column name>, '19700101'))

     

    -Brian



  • 3.  Re: Hello everybody, I'm trying to understand the Date field in the database, but i need to know when a User account was created, but this field only contains numbers(Integer), and I need to know how can I do if I need datetime value like 'YYYY/MM/DD'

    Posted 01-12-2018 01:56 PM

    Thanks!!! it Works!!!