-1

When I executed query below on SSMS I got error like Arithmetic overflow error converting expression to data type int

I am adding screen shots.

select  dateadd(second, 132706767376983975 /1000 + 8*60*60*60, '19700101')

enter image description here

whoami
  • 13
  • 2
  • 2
    132706767376983975 /1000 is bigger than MAX int ...... what would you expect to happen? – Mitch Wheat Jul 14 '21 at 07:11
  • I pulled this value from the lastLogon feature of users on AD but I need date formatted information can you propose any method for solve it? – whoami Jul 14 '21 at 07:19
  • 3
    I don't really know what you are trying to solve. Perhaps you have an XY problem? – Mitch Wheat Jul 14 '21 at 07:21
  • Question is clear! – whoami Jul 14 '21 at 07:41
  • 1
    @whoami What is the expected result? And what exactly is this number `132706767376983975`? – Zhorov Jul 14 '21 at 07:44
  • 1
    Given that todays date in Unix Epoch time is `1,626,249,414`, can you tell us what this enormous number represents? – Nick.Mc Jul 14 '21 at 07:57
  • This page https://social.technet.microsoft.com/wiki/contents/articles/12814.active-directory-lastlogontimestamp-conversion.aspx says that that number is _the number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 until the date/time that is being stored_ so your calc is incorrect – Nick.Mc Jul 14 '21 at 08:00
  • @Zhorov I have a third party application which takes and reports users' attributes from Active Directory,but I noticed that lastLogon values are 18 digit integers, i was able to see this value on datetime format by using powershell script.[datetime]::FromFileTime(132706767376983975) i want to make this operation via sql query – whoami Jul 14 '21 at 08:13
  • I'm trying to find the definition of what this number actually represents. I don't think the link above is actually correct. It's not Unix Epoch because it's too big and gives a date into the future – Nick.Mc Jul 14 '21 at 08:16
  • When I use this Powershell, as suggested in some links , I get a reasonable date `[datetime]::FromFileTime("132706767376983975")`. This link https://learn.microsoft.com/en-us/dotnet/api/system.datetime.fromfiletime?view=net-5.0 actually says the same thing: A Windows file time is a 64-bit value that represents the number of 100-nanosecond intervals that have elapsed since 12:00 midnight, January 1, 1601 A.D. (C.E. – Nick.Mc Jul 14 '21 at 08:18
  • The problem was solved via T-sql query below: Declare sayi2 int declare date char(30) set sayi2 = 1 set date = '1801/01/01' while(sayi2 <= 10000) begin set date = dateadd(second,13270676737698/10000000,@date) set sayi2 = sayi2 + 1 end set date = dateadd(second,3975/10000000,date) set date = dateadd(year,-200,date) select date – whoami Jul 14 '21 at 09:24

2 Answers2

0

The dateadd function expects to add an integer value as parameter.

Integer values are from -2,147,483,648 to 2,147,483,647, and your value is quite bigger than this, even divided by 1000.

If you want to use the dateadd function for this operation, you will need to split the add operation in several ones, all of them inside the allowed integer values, or use another datepart different than seconds.

James
  • 2,954
  • 2
  • 12
  • 25
0

I managed to get something remotely sensible using this calc, which rounds up to minutes:

DECLARE @Input BIGINT =  
132706767376983975 / 10000000

SELECT @Input = @Input / 60


select dateadd(minute, @Input, CAST('1601-01-01' AS DATETIME2(7)))

2021-07-13 19:05:00.0000000

This isn't quite the same as the correct answer though (powershell)

[datetime]::FromFileTime("132706767376983975")

Wednesday, 14 July 2021 5:05:37 AM

But I'm guessing this has to do with time zones.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91