Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-33465: How to convert system time and timestamp data to standard date/time format with sql query?

14 May,20 at 05:14 AM

Question: 
How to convert system time and timestamp data to standard date/time format with sql query?

User-added image
Answer:
In WashEvent table, we store the date as ticks. You can use this query to do the conversion...

SELECT CAST((SystemTime - 599266080000000000) / 864000000000 AS datetime) From dbo.WashEvent

Please note that the returned date is always in UTC.
You can ignore the value stored in the Timestamp column; it's an internal timer and not very useful if your aim is to find out events by their time.

For returning date in UTC ±, please refer to the sample queries below. 
If the timezone is UTC+, then use minus (-) before the "UTC timezone different*36000000000"
If the timezone is UTC-, then use plus (+) before the "UTC timezone different*36000000000"

SELECT CAST((SystemTime - (599266080000000000 ± UTC timezone different*36000000000)) / 864000000000 AS datetime) From dbo.WashEvent

e.g. Sample script for UTC+3

SELECT CAST((SystemTime - (599266080000000000 - 3*36000000000)) / 864000000000 AS datetime) From dbo.WashEvent 

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.

Related Articles

No related Articles