Convert a datetime column to number of seconds

Question

In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column. The long would represent a number of seconds.

Answer 1

Create a new column (ALTER TABLE) then run an UPDATE on it

UPDATE
  MyTable
SET
  NewIntColumn = DATEDIFF(sec, '19000101', MyDateTimeColumn)

19000101 is the SQL Server epoch. You can use 19700101 for Unix epoch for example

Answer 2

You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Borrowing @gbn's guesses on table/column names, here are a couple of different approaches that don't require constant maintenance.

Computed Column

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, '19000101', MyDateTimeColumn));

--or for Unix epoch

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, '19700101', MyDateTimeColumn));

You could also persist and index this column, trading off query performance for storage, but you'll need to make a slight change to the calculation (trying to persist the above will yield an error about the computation being non-deterministic):

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, 0, MyDateTimeColumn)) PERSISTED;

-- or for Unix epoch

ALTER TABLE dbo.MyTable ADD NewIntColumn AS
  CONVERT(INT, DATEDIFF(SECOND, 25567, MyDateTimeColumn)) PERSISTED;

You would want to persist the column if you are more concerned about read performance than write performance (or storage).

View

One benefit of a view over a new column is that you don't have to change the base table schema (or worry about keeping it up to date). You pay the cost of computation at query time, which is the same as a non-persisted computed column.

CREATE VIEW dbo.vMyTable
AS
  SELECT -- other columns,
    MyDateTimeColumn,
    NewIntColumn = DATEDIFF(...whichever calc above makes sense...)
  FROM dbo.MyTable;

Runtime

Since the above calculations are not overly complex, just include the calculation in your query. Hopefully you are using stored procedures for data access so you aren't repeating this often.