DB script for searching active users

[Erwin Manuel]

We would like to query for users whose last login was within 1 year from current date.

I found this query from docs:

SELECT

u.username AS username ,

psname.propertyvalue AS FullName ,

psemail.propertyvalue AS email,

pslogincount.propertyvalue AS logincount,

to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin

FROM

userbase u

JOIN

(

SELECT

id,

entity_id

FROM

propertyentry

WHERE

property_key = 'fullName'

)

entityName

ON

u.id = entityName.entity_id

JOIN

(

SELECT

id,

entity_id

FROM

propertyentry

WHERE

property_key = 'email'

)

entityEmail

ON

u.id = entityEmail.entity_id

left JOIN

(

SELECT

id,

entity_id

FROM

propertyentry

WHERE

property_key = 'login.count'

)

loginCount

ON

u.id = loginCount.entity_id

left JOIN

(

SELECT

id,

entity_id

FROM

propertyentry

WHERE

property_key = 'login.previousLoginMillis'

)

lastLogin

ON

u.id = lastLogin.entity_id

JOIN propertystring psname

ON

entityName.id=psname.id

JOIN propertystring psemail

ON

entityEmail.id = psemail.id

left JOIN propertystring pslogincount

ON

loginCount.id=pslogincount.id

left JOIN propertystring pslastlogin

ON

lastLogin.id = pslastlogin.id

ORDER BY username;

This seems to be what we need but the line "to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin" is not accepted in sql server. Does anyone knows whae query we can use to replace this line? Thanks in advance

[Erwin Manuel · Accepted solution]

I found the solution with the help of Atlassian support.

The equivalent in SQL of:

to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin

Is

dateadd(ss, cast( cast(pslastlogin.propertyvalue as varchar(1000)) as numeric) / 1000,'01/01/1970') as lastlogin

[Renjith Pillai]

http://beyondrelational.com/modules/2/blogs/70/posts/10957/sql-servers-equivalent-of-oracle-functions-part-ii.aspx

Says to use CONVERT

http://www.w3schools.com/sql/func_convert.asp

[Erwin Manuel]

I got: 'Arithmetic overflow error converting expression to data type datetime.'

Looks like its not what I need.