Query with date collum

Posted on 2011-10-08
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
    Shouldn't that be:

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

    Expert Comment

    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
    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) :
    LVL 3

    Accepted Solution

    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

    Work perfectly...

    LVL 51

    Expert Comment

    by:Mark Wills

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now