question

shaikhshoaib-6404 avatar image
0 Votes"
shaikhshoaib-6404 asked shaikhshoaib-6404 commented

SQL Server Converting Date, Time to UNIX Epoch time (Pre-SQL 2016)

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered shaikhshoaib-6404 commented

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'


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great, thanks, this seems to be working

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered shaikhshoaib-6404 commented

Hi @shaikhshoaib-6404

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi thanks but bigint does not work on pre-SQL Server 2016

0 Votes 0 ·