SELECT
U.ID,
case when isdate(U.TIMESTAMP_X)=0 then null else
REPLICATE('0',2-LEN(DATEPART(dd, U.TIMESTAMP_X)))+CONVERT(varchar(2),DATEPART(dd, U.TIMESTAMP_X))+'.'+
REPLICATE('0',2-LEN(DATEPART(mm, U.TIMESTAMP_X)))+CONVERT(varchar(2),DATEPART(mm, U.TIMESTAMP_X))+'.'+
CONVERT(varchar(4),DATEPART(yyyy, U.TIMESTAMP_X))+' '+
REPLICATE('0',2-LEN(DATEPART(hh, U.TIMESTAMP_X)))+CONVERT(varchar(2),DATEPART(hh, U.TIMESTAMP_X))+':'+
REPLICATE('0',2-LEN(DATEPART(mi, U.TIMESTAMP_X)))+CONVERT(varchar(2),DATEPART(mi, U.TIMESTAMP_X))+':'+
REPLICATE('0',2-LEN(DATEPART(ss, U.TIMESTAMP_X)))+CONVERT(varchar(2),DATEPART(ss, U.TIMESTAMP_X))
end TIMESTAMP_X,
U.LOGIN,
U."PASSWORD",
U.CHECKWORD,
U.ACTIVE,
U.NAME,
U.LAST_NAME,
U.EMAIL,
case when isdate(U.LAST_LOGIN)=0 then null else
REPLICATE('0',2-LEN(DATEPART(dd, U.LAST_LOGIN)))+CONVERT(varchar(2),DATEPART(dd, U.LAST_LOGIN))+'.'+
REPLICATE('0',2-LEN(DATEPART(mm, U.LAST_LOGIN)))+CONVERT(varchar(2),DATEPART(mm, U.LAST_LOGIN))+'.'+
CONVERT(varchar(4),DATEPART(yyyy, U.LAST_LOGIN))+' '+
REPLICATE('0',2-LEN(DATEPART(hh, U.LAST_LOGIN)))+CONVERT(varchar(2),DATEPART(hh, U.LAST_LOGIN))+':'+
REPLICATE('0',2-LEN(DATEPART(mi, U.LAST_LOGIN)))+CONVERT(varchar(2),DATEPART(mi, U.LAST_LOGIN))+':'+
REPLICATE('0',2-LEN(DATEPART(ss, U.LAST_LOGIN)))+CONVERT(varchar(2),DATEPART(ss, U.LAST_LOGIN))
end LAST_LOGIN,
case when isdate(U.DATE_REGISTER)=0 then null else
REPLICATE('0',2-LEN(DATEPART(dd, U.DATE_REGISTER)))+CONVERT(varchar(2),DATEPART(dd, U.DATE_REGISTER))+'.'+
REPLICATE('0',2-LEN(DATEPART(mm, U.DATE_REGISTER)))+CONVERT(varchar(2),DATEPART(mm, U.DATE_REGISTER))+'.'+
CONVERT(varchar(4),DATEPART(yyyy, U.DATE_REGISTER))+' '+
REPLICATE('0',2-LEN(DATEPART(hh, U.DATE_REGISTER)))+CONVERT(varchar(2),DATEPART(hh, U.DATE_REGISTER))+':'+
REPLICATE('0',2-LEN(DATEPART(mi, U.DATE_REGISTER)))+CONVERT(varchar(2),DATEPART(mi, U.DATE_REGISTER))+':'+
REPLICATE('0',2-LEN(DATEPART(ss, U.DATE_REGISTER)))+CONVERT(varchar(2),DATEPART(ss, U.DATE_REGISTER))
end DATE_REGISTER,
U.LID,
U.PERSONAL_PROFESSION,
U.PERSONAL_WWW,
U.PERSONAL_ICQ,
U.PERSONAL_GENDER,
U.PERSONAL_BIRTHDATE,
U.PERSONAL_PHOTO,
U.PERSONAL_PHONE,
U.PERSONAL_FAX,
U.PERSONAL_MOBILE,
U.PERSONAL_PAGER,
U.PERSONAL_STREET,
U.PERSONAL_MAILBOX,
U.PERSONAL_CITY,
U.PERSONAL_STATE,
U.PERSONAL_ZIP,
U.PERSONAL_COUNTRY,
U.PERSONAL_NOTES,
U.WORK_COMPANY,
U.WORK_DEPARTMENT,
U.WORK_POSITION,
U.WORK_WWW,
U.WORK_PHONE,
U.WORK_FAX,
U.WORK_PAGER,
U.WORK_STREET,
U.WORK_MAILBOX,
U.WORK_CITY,
U.WORK_STATE,
U.WORK_ZIP,
U.WORK_COUNTRY,
U.WORK_PROFILE,
U.WORK_LOGO,
U.WORK_NOTES,
U.ADMIN_NOTES,
U.STORED_HASH,
U.XML_ID,
U.CONFIRM_CODE,
U.LAST_ACTIVITY_DATE,
case when isdate(U.PERSONAL_BIRTHDAY)=0 then null else
REPLICATE('0',2-LEN(DATEPART(dd, U.PERSONAL_BIRTHDAY)))+CONVERT(varchar(2),DATEPART(dd, U.PERSONAL_BIRTHDAY))+'.'+
REPLICATE('0',2-LEN(DATEPART(mm, U.PERSONAL_BIRTHDAY)))+CONVERT(varchar(2),DATEPART(mm, U.PERSONAL_BIRTHDAY))+'.'+
CONVERT(varchar(4),DATEPART(yyyy, U.PERSONAL_BIRTHDAY))
end PERSONAL_BIRTHDAY,
U.EXTERNAL_AUTH_ID,
U.CHECKWORD_TIME,
U.SECOND_NAME,
U.LOGIN_ATTEMPTS
FROM
b_user U
INNER JOIN (SELECT DISTINCT U.ID
FROM
b_user U
INNER JOIN b_utm_user BUF1 ON BUF1.FIELD_ID = 5 AND BUF1.VALUE_ID = U.ID
WHERE
(1=1
AND
(
(
(upper(convert(varchar(8000), U.ID)) = upper(convert(varchar(8000), '695')) and U.ID is not null)
)
)
AND
(
(BUF1.VALUE_INT IS NOT NULL AND BUF1.VALUE_INT <> 0)
)
)
) U2 ON U2.ID = U.ID
ORDER BY U.ID
|