<div>
Shouldn't that be: 
 
CAST((lastLogonTimestamp / 864000000000.0 - 109207) AS DATETIME) AS [Ultimo Login]
</div>

<div>
What type is that lastLogonTimestamp, and what exactly are you trying to do with that calculation? &nbsp;I'm sure that's where your error is coming from. 
Can you post an example value so that we have something to play with? 
 
@dale: both 'alias' and [alias] are valid T-SQL.
</div>

<div>
Yeah, it looks like it is your lastlogontimestamp 
 
Would normally do something like : CAST(lastLogonTimestamp AS bigint) / 600000000 - 157258080 
 
or to convert to datetime, dateadd(mi, CAST(lastLogonTimestamp AS bigint) / 600000000 - 157258080, 0) 
 
wouldnt try to default to floating arithmetic to do those datetime calcs... 
 
There is a handy little article if you are into datetime junk (like me) : <a href="http://myitforum.com/cs2/blogs/jnelson/archive/2009/08/25/140938.aspx" rel="ugc">http://myitforum.com/cs2/blogs/jnelson/archive/2009/08/25/140938.aspx</a> 

</div>

<div>
Work perfectly... 
 
Tks
</div>

<div>
Interesting... 
 
Find the need to convert(numeric(28,0),864000000000.0) most intriguing. 
 
Your actual problem was not handling lastlogontimestamp as a number it can cope with - ie it is int8 and that is why you needed to CAST(lastLogonTimestamp AS bigint) or, in your selected solution, numeric(28,0) 
 
The real answer was simply converting lastlogintimestamp. 
 
To go to seconds then you do need slightly different arithmetic (date functions are basically integer) 
 
But try this quick comparison : 
 
DECLARE @Int8 BIGINT 
SET @Int8 = 128462210724001300 
 
SELECT DATEADD(mi,(@Int8 / 600000000) - 157258080 ,0) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- minutes 
SELECT CONVERT( DATETIME, (cast( @int8 as bigint ) / 864000000000 - 109207)) 
SELECT CAST((convert(numeric(28, 0), @int8 ) / convert(numeric(28, 0), 864000000000.0) - convert(numeric(28, 0), 109207)) AS DATETIME) 
 
Hope that clarifies things a bit more... 
&nbsp; 
 

</div>

<div>
<div class="content wysiwyg-content">
Hi, 
 
I have a problem in one sql adsi query with datetime type, if i insert a cast to convert the lastlogon collum to date and time format the query don´t work. 
 
QUERY 
select 
CAST((lastLogonTimestamp / 864000000000.0 - 109207) AS DATETIME) AS 'Ultimo Login', 
sAMAccountName as 'Estação de Trabalho', 
ExtensionAttribute7 as 'Data Liberação - Atributo7', 
ExtensionAttribute8 as 'Aprovador - Atributo8', 
ExtensionAttribute9 as 'Executor - Atributo9' 
from 
openquery 
(ADSI,'SELECT lastLogonTimestamp, sAMAccountName, ExtensionAttribute7, ExtensionAttribute8, ExtensionAttribute9 
FROM ''LDAP://OU=PROSUBEBN,OU=RJ,OU=BR,OU=Odebrecht,DC=odebrecht,DC=net'' 
WHERE objectCategory=''computer'' 
and memberOf=''CN=EBN-Allow-USB-CD-Read,OU=GROUPS,OU=PROSUBEBN,OU=RJ,OU=BR,OU=Odebrecht,DC=odebrecht,DC=net''') 
 
ERROR 
Msg 8115, Level 16, State 8, Line 1 
Arithmetic overflow error converting nvarchar to data type numeric.
</div>
</div>

Hi, 

I have a problem in one sql adsi query with datetime type, if i insert a cast to convert the lastlogon collum to date and time format the query don´t work.

QUERY
select 
CAST((lastLogonTimestamp / 864000000000.0 - 109207) AS DATETIME) AS 'Ultimo Login', 
sAMAccountName as 'Estação de Trabalho',
ExtensionAttribute7 as 'Data Liberação - Atributo7', 
ExtensionAttribute8 as 'Aprovador - Atributo8', 
ExtensionAttribute9 as 'Executor - Atributo9'
from
openquery
(ADSI,'SELECT lastLogonTimestamp, sAMAccountName, ExtensionAttribute7, ExtensionAttribute8, ExtensionAttribute9 
FROM ''LDAP://OU=PROSUBEBN,OU=RJ,OU=BR,OU=Odebrecht,DC=odebrecht,DC=net''
WHERE objectCategory=''computer'' 
and memberOf=''CN=EBN-Allow-USB-CD-Read,OU=GROUPS,OU=PROSUBEBN,OU=RJ,OU=BR,OU=Odebrecht,DC=odebrecht,DC=net''')

ERROR
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.

Query with date collum

Active Directory (AD) is a Microsoft brand for identity-related capabilities. In the on-premises world, Windows Server AD provides a set of identity capabilities and services, and is hugely popular (88% of Fortune 1000 and 95% of enterprises use AD). This topic includes all things Active Directory including DNS, Group Policy, DFS, troubleshooting, ADFS, and all other topics under the Microsoft AD and identity umbrella.

Active Directory

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.