1

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?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • 1
    why not just use `select datediff(day,'1900/01/01',ev.StartDate)` and avoid it all together.... – S3S May 30 '17 at 20:44
  • 1
    If you are converting dates to integers, you are going for DAY whether you know it or not ;) – Jacob H May 30 '17 at 20:46
  • 1
    `SELECT CAST(CAST('2017-05-30 00:00:00.000' as datetime) AS decimal(8,2))` will return `42883` and `SELECT CAST(CAST('2017-05-30 12:00:00.000' AS datetime) as decimal(8,2))` will return `42883.50` so each day is represented as a single number, starting at the year 1900. – Jacob H May 30 '17 at 20:52

4 Answers4

5

You can cast the startdate as datetime for conversion.

CONVERT(BIGINT,CAST(ev.StartDate as DATETIME)) * -1
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
2

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
2

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You might require to convert to varchar and then bigint 

select Convert(bigint,convert(varchar(10),ev.StartDate,112))*(-1)
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38