Search code examples
sqlstringssissql-server-2016unix-timestamp

SQL converting date/time to unix timestamp

im running an SSIS job that is pulling in multiple string columns into a SQL sever 2016 database, however i need to create a row index so that my job knows when to get new data. i have a date/time string column that im pulling in. i want to convert this to a unix timestamp so that i can use that in my code for when to grab new data.

is there a way to do this? my string column with the date/time that im pulling in looks like this: '2020.07.29 08:43:53' '2021.05.03 12:50:22' etc..

what would be the best way to convert all the strings in my 'datetime' column to unix timestamps?

thanks

Solution

  • You can convert the string to a datetime using:

    select convert(datetime, '2021.05.03 12:50:22')

    You can then convert this to Unix epoch time (seconds since 1970-01-01) using:

    select datediff(second, '1970-01-01', convert(datetime, '2021.05.03 12:50:22'))

    Here is a db<>fiddle.