Explicit conversion from data type date to bigint is not allowed

Question

This used to work with a column type of DATEIME but now it won't with DATE.

CONVERT(BIGINT,ev.StartDate) * -1

Is there anyway to get a BIGINT value from a DATE column?

Answer 1

Yet another option. This will even flip the sign for you

Example

Declare @YourTable table (StartDate date)
Insert Into @YourTable values ('2017-05-30')

Select DateDiff(DAY,StartDate,-1)
 From @YourTable

Returns

-42884

Answer 2

You can cast the startdate as datetime for conversion.

CONVERT(BIGINT,CAST(ev.StartDate as DATETIME)) * -1

Answer 3

You might require to convert to varchar and then bigint

select Convert(bigint,convert(varchar(10),ev.StartDate,112))*(-1)

Answer 4

First, dates in SQL Server are counted by days from the year 1900. A big int starts to be useful at about 2.1 billion. That corresponds to a year in the range of 5.8 million. Do you really have dates that large?

Of course, casting to an int is not permitted. You can cast datetime values . . . but are there other ways?

One simple way is:

select 1 + datediff(day, 0, datecol)

The "+ 1" is needed so the value matches the actual conversion. (You can use "-1" instead of "0" instead.)

Or, perhaps you want Unix time in seconds or milliseconds. For that:

select datediff_big(ms, '1970-01-01', datecol)