-1

I have a table that has a field Report_Date. This field is a bigint type. I have another table that has ReportDate that is datetime type. I want to combine data from each table, but I want the bigint converted into a datetime.

I tried SELECT DATEADD(DD, convert(bigint, Report_Date), Report_date) however I get the error message:

Arithmetic overflow error converting expression to data type datetime.

I have also tried SELECT DATEADD(DD, convert(bigint, Report_Date), convert(datetime, Report_date)) with the same error message result.

I expect the date time to be 2019-02-28 00:00:00.000.

Dale K
  • 25,246
  • 15
  • 42
  • 71
user7577070
  • 97
  • 3
  • 9
  • You must have some data in the table that is not a valid datetime value. We don't know what your bigint "dates" look like. Help us to help you by giving us some sample data. – Sean Lange May 15 '19 at 20:50
  • Each row has a Report_Date that is in this form `yyyymmdd` one example '20190108' – user7577070 May 15 '19 at 20:56
  • 2
    If it is a bigint why are you converting it to a bigint? And you might want to take a look at the date you get back when you convert your bigint value to a datetime. Hint...it isn't what you think. You should convert it to a varchar first. – Sean Lange May 15 '19 at 21:02
  • Convert it to char(8) and then to datetime. – Luis Cazares May 15 '19 at 21:03
  • Thank you! I was looking through stackoverflow and trying to find a solution, hence the reason convert to bigint as my skills are still developing with writing SQL. – user7577070 May 16 '19 at 02:29

2 Answers2

2

For your example you would need to do something like this.

select convert(datetime, convert(char(8), 20190108))

I can't for the life of me figure out what you are trying to do with your DATEADD logic there.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
1

To cast bigint/int to datetime you firstly need to cast it to varchar. You can do it i.e. like this:

select cast(cast(Report_Date as varchar(80)) as datetime)
M. Kanarkowski
  • 2,155
  • 8
  • 14