How to query IdentityIQ dates in a Microsoft SQL Server (MSSQL) database

[#1]

SailPoint stores many dates as Unix timestamps rather than a date-specific data type.

MSSQL does not have Oracle or MySQL's nice parsing functions for handling Unix timestamps. Further complicating matters, all Unix timestamps are GMT+0 (UTC) offset by definition, which may be offset from either your IIQ or database server's time zone (which themselves don't even have to be the same).

Here's the pattern to query a date, e.g. from SPT_IDENTITY:

DATEADD(S, SPT_IDENTITY.CREATED / 1000, dateadd(s, 0, '1970-01-01') at time zone 'UTC') at time zone 'your time zone'

What you will get is an MSSQL DATETIMEOFFSET object, which consists of a date, a time, and a timezone offset. These can be safely compared to other DATETIMEOFFSET objects (taking time zone into account) or other non-offset MSSQL date types (which will be assumed to be at the database's local time zone).

For example, if the value of CREATED is 1562954414006, the conversion above using "Eastern Standard Time" will give 2019-07-12 14:00:14.000 -04:00. (Observe that SQLServer automatically takes care of Daylight Savings Time conversions.)

Note: You won't be able to use this structure from Hibernate HQL, since it doesn't recognize most database functions. This would be used, for example, with a SQL-based report from outside of IIQ.

[a101090]

Hi, May I know how and where to use this? I am using MSSQL Server Management Studio to run the above, seems like the DateADD stored procedure is not present.

Thanks,

Amit

[kruegja]

Hey Amit, have you tried it in a whole script like this:

declare @StartDate as datetime

SELECT @StartDate = DATEADD(MI,datepart(tz,sysdatetimeoffset()),DATEADD(S,1512708537435/1000,'19700101'))

select @StartDate