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'

[Legacy User]

#Date #Help 

[Brian_Fontana]

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

[Legacy User]

Thanks!!! it Works!!!