-1

I've been having trouble with something i cannot quite get my head around and I was wondering if you can help?

On my table, i have a column called

ADDEDDATE

This column shows the date in which an incident or ticket was added to the database table and its formatting is shown as the following example:

2015-08-25 09:58:14.967

I want to convert this to UNIX timestamp so first i have performed the following:

SELECT DATEDIFF(SECOND,{d '1970-01-01'}, ADDEDDATE) AS 'UNIX DATE ADDED' FROM dbo.TABLE

Running this I get integer values such as "1147855575". Now I understand in order to make this work on UNIX timestamp i need to use BIGINT to convert, so i wrote the following:

SELECT DATEDIFF(SECOND,{d '1970-01-01'}, ADDEDDATE) AS 'UNIX DATE ADDED',
CASE CAST(SUM('UNIX DATE ADDED' AS BIGINT) FROM dbo.TABLE

This returned an error:

Msg 195, Level 15, State 10, Line 2 'SUM' is not a recognized built-in function name.

So i did some googling and searching through Stackoverflow and found i did the second line wrong, it should look like:

SELECT DATEDIFF(SECOND,{d '1970-01-01'}, ADDEDDATE) AS 'UNIX DATE ADDED',
CASE CAST(BIGINT,'UNIX DATE ADDED') FROM dbo.TABLE

However this too fails with the following message:

Msg 1035, Level 15, State 10, Line 2 Incorrect syntax near 'CAST', expected 'AS'.

Can someone please assist me in trying to convert an entire column of integer data to BIGINT for UNIX Datestamp? I am using MSSQL (SSMS 2014).

TGrantham
  • 1
  • 2
    simply `datediff(second, '1970-01-01', ADDEDDATE)` – Squirrel Sep 22 '17 at 05:30
  • 1
    The case, incorrectly placed before the cast. may be what you want is SELECT cast(DATEDIFF(SECOND,{d '1970-01-01'}, ADDEDDATE) as bigint) AS 'UNIX DATE ADDED' FROM dbo.TABLE – Ahmed Saeed Sep 22 '17 at 05:42
  • Ahmed Saeed, unfortunately your solution returns the exact same 10 number long result as the original SELECT DATEDIFF(SECOND,{d '1970-01-01'}, ADDEDDATE) AS 'UNIX DATE ADDED'. Squirrel can you please expand on your explanation? – TGrantham Sep 22 '17 at 06:06

1 Answers1

0

Just:

SELECT CAST(DATEDIFF(SECOND, '1970-01-01', ADDEDDATE) AS bigint) AS 'UNIX DATE ADDED' FROM dbo.TABLE;

When you'll migrate on SQL Server 2016, you'll be able to use DATEDIFF_BIG

Ruslan K.
  • 1,912
  • 1
  • 15
  • 18