PostgreSQL to SQL Server Migration Error Caused by Default Value Expression

Question

I am working on integrating databases from both MySQL and Postgres into SQL Server. My data migration from Postgres to SQL Server is failing because of the default value expression in Postgres.

This is the default value expression: date_part('epoch'::text, now())

Does anyone know what the SQL Server equivalent is? I managed to successfully migrate the data using using date_part('epoch'::text, now())=1 though I do not know if this is the real equivalent?

Can someone please tell me if this is in fact the SQL Server equivalent?

Answer 1

For "normal" (day, week, month, year, ...) in T-SQL you'd use the DATEPART function. (Check DATEPART (Transact-SQL)). However, this function doesn't deal with 'epoch'.

The definition of date_part('epoch'::text, now()) in PostgreSQL is: `time elapsed since the "epoch time" (1 Jan 1970), measured in seconds. In T-SQL, you would compute it with:

DATEDIFF(second,'19700101 00:00:00:000', getutcdate())

Answer 2

This seems to get you a similar result:

DATEDIFF(SECOND, '1970-01-01T00:00:00', SYSDATETIME()) 
   + DATEPART(NANOSECOND, SYSDATETIME())/1000000000.0

However, what are you actually attempting to do with that number? If you want a serial number, you might want to define the column as a rowversion