Error en registro de la hora

Cuando creo usuarios en la tabla aspnet_Membership observo que los
campos tipo "datetime" como LastLoginDate registran perfectamente el
día pero no la hora. Me parece que el problema está en SET
@CreateDate = @CurrentTimeUtc del Procedimiento Almacenado que utiliza
esta tabla para registrar nuevos usuarios y que adjunto líneas abajo.

LastLoginDate
-
2009-09-12 08:16:39.000
2009-09-12 08:17:01.860
2009-09-16 05:48:08.233
2009-09-16 05:31:26.983
2009-09-16 05:26:39.063
-
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser]
Script Date: 09/16/2009 01:09:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime,
'17540101',
112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime,
'17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName,
@ApplicationId OUTPUT

IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc

SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE
LOWER(@UserName)
= LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser
@ApplicationId,
@UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId

IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK,
HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND
LoweredEmail > LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0

Cleanup:

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode

END

Hola Alexa,

¿Que quieres decir con que no se registra correctamente? A priori tanto
las fechas y horas que muestras en la columna LastLoginDate no tienen nada
incorrecto a simple vista...

Un saludo,

Rubén Garrigós
Solid Quality Mentors

Blog: http://blogs.solidq.com/es/elrincondeldba

Cuando creo usuarios en la tabla aspnet_Membership observo que los
campos tipo "datetime" como LastLoginDate registran perfectamente el
día pero no la hora. Me parece que el problema está en SET
@CreateDate = @CurrentTimeUtc del Procedimiento Almacenado que utiliza
esta tabla para registrar nuevos usuarios y que adjunto líneas abajo.

LastLoginDate
-
2009-09-12 08:16:39.000
2009-09-12 08:17:01.860
2009-09-16 05:48:08.233
2009-09-16 05:31:26.983
2009-09-16 05:26:39.063
-
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser]
Script Date: 09/16/2009 01:09:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime,
'17540101',
112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime,
'17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName,
@ApplicationId OUTPUT

IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc

SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE
LOWER(@UserName)
= LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser
@ApplicationId,
@UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId

IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK,
HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND
LoweredEmail >> LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0

Cleanup:

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode

END