Sql-server – Convert a datetime column to number of seconds

datetimesql serversql-server-2008t-sqltype conversion

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.

I thought if I can convert it to longs, it would make it easier to do group by queries over time periods, as I could just divide the long number by fixed amounts.

The table is static, Won't be updating or deleting data.

Best Answer

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

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

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

Related Solutions

SQL Server – Convert varchar Column to datetime

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

Sql-server – Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.

Something like this:

declare @S varchar(50)
set @S = '20120606122012'

select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)

Best Answer

Related Solutions

SQL Server – Convert varchar Column to datetime

Sql-server – Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

Related Question