Learn how to a build a cloud-first strategyRegister Now


Query with date collum

Posted on 2011-10-08
Medium Priority
Last Modified: 2012-05-12

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.

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'
(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''')

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
Question by:ThyagoCoan
LVL 21

Expert Comment

by:Dale Burrell
ID: 36937749
Shouldn't that be:

CAST((lastLogonTimestamp / 864000000000.0 - 109207) AS DATETIME) AS [Ultimo Login]
LVL 37

Expert Comment

ID: 36938241
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.
LVL 51

Expert Comment

by:Mark Wills
ID: 36938270
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.


Accepted Solution

jvejskrab earned 1000 total points
ID: 36941359
CAST((convert(numeric(28, 0), lastLogonTimestamp ) / convert(numeric(28, 0), 864000000000.0) - convert(numeric(28, 0), 109207)) AS DATETIME) AS [Ultimo Login]

Author Closing Comment

ID: 36941921
Work perfectly...

LVL 51

Expert Comment

by:Mark Wills
ID: 36942587

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 :

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...


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to deal with a specific error when using the Enable-RemoteMailbox cmdlet to create a mailbox in the cloud-based service, for an existing user in an on-premises Active Directory.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question