If you believe that bigint is not always available, then try something like this:
select cast(datediff(s, '1970-01-01', event_time) as varchar(11)) + '000'
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I am currently trying to convert SQL server (2008 R2 and 2012) audit logs from .sqlaudit file to csv/json/txt via PowerShell sqlcmd command. There is no issues doing this.
Something like this:
Invoke-sqlcmd -ServerInstance ...I -Query "SELECT * FROM sys.fn_get_audit_file ('\path\Audit-20210210-210446_F3B4AE48-04C4-4A14-AE30-F1AEB2AFF123_0_132580264066030000.sqlaudit',default,default);" | ConvertTo-Json -Depth 1 | out-file .....
However I need the date output in UNIX epoch time format (in milli sec).
I tried the datediff command however SELECT DATEDIFF(s,'1970-01-01',event_time) * 1000 does not work as this 13-digit value exceeds SQL integer limit.
I know form SQL 2016 there is big int, however older sql version does not have this.
Wondering what are the options?
I might try via PS on a different script - not sure how Win 2012 PS handled big int/conversion to big int.
Thanks
If you believe that bigint is not always available, then try something like this:
select cast(datediff(s, '1970-01-01', event_time) as varchar(11)) + '000'
Hi @shaikh shoaib ,
Welcome to Microsoft Q&A!
You could use cast() or convert() as below:
SELECT DATEDIFF(s,'1970-01-01',event_time) * convert(bigint, 1000)
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.