Share via

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

shaikh shoaib 171 Reputation points
2021-03-02T23:53:26.823+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,017 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,571 questions
0 comments
Accepted answer
  1. Viorel 113.7K Reputation points
    2021-03-03T09:41:40.897+00:00

    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 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-03-03T01:36:03.417+00:00

    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.