DATETIME value displayed as incorrectly
am 31.01.2005 12:27:51 von AndyI have a SQL Server 2000 table with the following structure:
CREATE TABLE [Personal] (
[UserId] [INT] IDENTITY (1, 1) NOT NULL ,
[Name] [NVARCHAR] (50),
[Surname] [NVARCHAR] (50),
[SomeDate] [DATETIME]
)
UserId is the primary key.
The user captures the date in YYYY/MM/DD format in a field on Test1.asp
and then submits the page to Test2.asp.
Test2.asp removes the '/' characters from the date using
Replace(Request.Form("SomeDate"),"/","") and then inserts the date value
in YYYYMMDD format into the database using:
CREATE PROCEDURE dbo.UpdatePersonal
@UserId INT, @Name NVARCHAR(50), @Surname NVARCHAR(50), @SomeDate
DATETIME
AS BEGIN
SET NOCOUNT ON
UPDATE Personal
SET Name = @Name, Surname = @Surname, SomeDate = @SomeDate
WHERE UserId = @UserId
END
GO
If the user goes back to Test1.asp, it retrieves all the info that was
captured from the database using:
CREATE PROCEDURE dbo.GetDataPersonal
@UserId INT = NULL
AS
BEGIN
SET NOCOUNT ON
SELECT UserId, Name, Surname, SomeDate
FROM Personal
WHERE UserId = @UserId
END
GO
The problem is that the 'SomeDate' value is displayed as DD/MM/YYYY on
Test1.asp instead of YYYY/MM/DD. This only happens on the Production
server and I haven't been able to reproduce it on the Dev box.
When I run "SELECT SomeDate FROM Personal" in Query Analyser it returns
the SomeDate column as '1945-01-20 00:00:00.000' which is correct for a
DateTime column.
Are the Regional Settings on the production server the cause of the
problem? If so, is there a way to make the code totally independant of
the Servers' regional settings?
---
Extra info:
UpdatePersonal is an UPDATE procedure because the initial record is
created at the beginning of the users session using procedure
InsertInitial:
CREATE PROCEDURE dbo.InsertInitial
@UserId INT, @Name NVARCHAR(30), @Surname NVARCHAR(30), @SomeDate
DATETIME
AS
BEGIN
SET NOCOUNT ON
INSERT Personal (UserId,Name,Surname,SomeDate)
VALUES (@UserId,@Name,@Surname,@SomeDate)
SELECT @UserId = SCOPE_IDENTITY()
SELECT UserId = @UserId
INSERT General (UserId) VALUES (@UserId)
INSERT Name (UserId) VALUES (@UserId)
INSERT Surname (UserId) VALUES (@UserId)
INSERT SomeDate (UserId) VALUES (@UserId)
END
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!