I have been playing around with the Membership tables and saw your request... consider the following procedure. The following stored procedure will return the username (for conformation) and the LastLoginDate adjusted by the timezone value associated with the user. The default if none is found is 0.
You should be able to tweak this procedure as you need. Just use the script in the Host>SQL window ..
I have been rather explicit in the way I built the proc so you can see where all the intermediate field come from ... you can combine some of these to possibly improve performance (??) but I prefer the readability over the slight gain you may get.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLastLoginDateByUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetLastLoginDateByUser]
go
Create Procedure [dbo].[GetLastLoginDateByUser]
@PortalId int,
@UserId int
as
-- exec [GetLastLoginDateByUser] 2, 977
declare @PropertyDefinitionID as int
declare @TimeZoneAdj as int
declare @LastLoginDate as DateTime
declare @Username as varchar(128)
declare @ASP_ApplicationID as uniqueidentifier
declare @ASP_UserID as uniqueidentifier
set @PropertyDefinitionID = (Select PropertyDefinitionID from dbo.ProfilePropertyDefinition where PropertyName = 'TimeZone' and PortalID = @PortalId)
set @TimeZoneAdj = Convert(int, (select PropertyValue from UserProfile where UserID = @UserId and PropertyDefinitionID = @PropertyDefinitionID))
if @TimeZoneAdj is Null
set @TimeZoneAdj = 0
set @Username = (select Username from dbo.Users where UserID = @UserID)
set @ASP_ApplicationID = (select applicationID from dbo.aspnet_Applications where ApplicationName = 'DotNetNuke')
set @ASP_UserID = (select UserId from dbo.aspnet_Users where Username = @Username and ApplicationID = @ASP_ApplicationID)
set @LastLoginDate = (select LastLoginDate from dbo.aspnet_Membership where UserID = @ASP_UserID and ApplicationID = @ASP_ApplicationID)
Select @UserName as Username, Dateadd(n, @TimeZoneAdj, @LastLoginDate) as LastLoginDate
Return 0
Hope that helps....
Paul.