Query with date collum
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=R
WHERE objectCategory=''computer'
and memberOf=''CN=EBN-Allow-US
ERROR
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
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=R
WHERE objectCategory=''computer'
and memberOf=''CN=EBN-Allow-US
ERROR
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
What type is that lastLogonTimestamp, and what exactly are you trying to do with that calculation? 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.
Can you post an example value so that we have something to play with?
@dale: both 'alias' and [alias] are valid T-SQL.
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) : http://myitforum.com/cs2/blogs/jnelson/archive/2009/08/25/140938.aspx
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) : http://myitforum.com/cs2/blogs/jnelson/archive/2009/08/25/140938.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Work perfectly...
Tks
Tks
Interesting...
Find the need to convert(numeric(28,0),8640
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) -- 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...
Find the need to convert(numeric(28,0),8640
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) -- 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...
CAST((lastLogonTimestamp / 864000000000.0 - 109207) AS DATETIME) AS [Ultimo Login]