Wrong date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wrong date format

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 05:24:38

Hello All,

I have an asp page with a hidden field that holds <%=Date()%>. Because the Session.LCID is set to UK this value today would be 12/03/2007 (UK format).

When this hidden field is fed into an INSERT Stored Procedure in SQL Server in my testing environment, the date format that is inserted into the database is the same as the value in the hidden field eg 12/03/2007, which is what I want.

Now though, the site has moved to a production web server with SQL Server. When I perform this exact insert using the same webpages and (from what I can see) the same SQL Server configuration, the date inserts as 03/12/2007, US format. This is causing me big problems as the website is complete but the wrong dateformat is producing some undesirable results. The whole site is set up to expect the original format and I cannot see why this is happening.

Does anyone have any suggestions and more importantly how I can change this please.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-12 : 05:33:00

can u post ur query? or while insertion have u converted date format to desired format?

Mahesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 05:42:50

always use Universal foramt YYYYMMDD and you will not have any issue

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 05:45:42

Mahesh, here is the SP:


CREATE PROCEDURE [dbo].[usp_InsertUser]
@Title Varchar(7), @Account_name Varchar(60), @Account_Contact Varchar(50), @FirstName Varchar(30), @MiddleName Varchar(30),
@Surname Varchar(30), @ProfileType Varchar (10), @EntryDate datetime, @AddressName_No Varchar(30), @Address_Road varchar(30),
@Address_1 Varchar(30), @Address_2 Varchar(30), @Address_County Varchar(30), @Address_Postcode Varchar(10), @Address_Country Varchar(20), 
@Telephone1 Varchar(14), @Telephone2 varchar(14), @fax Varchar(14), @Email Varchar(45), @CompanyName Varchar(40), @CompanyAddress Varchar(200),
@Spec_Requirements Varchar(100), @Message Varchar(200), @WebEnabled Char(1), @Enabled Char(1), @CreatedBy Varchar(30), @CardAbbr Varchar(15)
as
INSERT INTO FyldeDB.dbo.Customer_Details (Title, Account_Name, Account_Contact, FirstName, MiddleName, Surname, ProfileType, EntryDate, AddressName_No, Address_Road, 
Address_1, Address_2, Address_County, Address_Postcode, Address_Country, Telephone1, Telephone2, Fax, Email, CompanyName, CompanyAddress, Spec_Requirements,
Message, WebEnabled, Enabled, CreatedBy, CardAbbr)
	Values (@Title, @Account_name, @Account_Contact, @FirstName, @MiddleName, @Surname, @ProfileType, @EntryDate, @AddressName_No, @Address_Road, 
@Address_1, @Address_2, @Address_County, @Address_Postcode, @Address_Country, @Telephone1, @Telephone2, @fax, @Email, @CompanyName, @CompanyAddress, 
@Spec_Requirements, @Message, @WebEnabled, @Enabled, @CreatedBy, @CardAbbr)

Khtan, yes I see that now, but at this point would mean a complete re-write of many of the asp pages etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 05:54:19

use parameter. see http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-12 : 05:54:47

in a SProc add

Set DateFormat mdy
Go

try this...

Mahesh

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 06:01:20

This looks a great article Khtan but is there not an easier solution to my problem? Can a simple format somewhere in the SP not do what I need?

I cannot understand how this all works fine on my dev database but not on the production database.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 07:11:32

Hi Mahesh,

Thanks but this does not work. The date in the hidden field is in dmy format right up until it is inserted, then it changes to mdy. Why though, only on the production database? It is fine on my test database.

Could I wrap a format around the date in the Stored Proc? Would this work?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-12 : 08:28:08

Your ASP code needs to handle converting things to proper data datatypes when calling this stored procedure, it has nothing to do with T-SQL, or T-SQL data formats or server settings. It may have to do with date settings on your web server, but date settings on your SQL Server should never matter because you should always deal with datetime datatypes in T-SQL.

The good news is -- you are using a stored procedure, using parameters, and using proper datatypes. You are WAY ahead of the curve already compared to 90% of the code out there, and in good shape because of that! All you need to do in your ASP code is ensure that you are creating an accurate datetime value THERE, and then pass that value to your stored proc as a parameter. So, just debug and look at your ASP code and be sure that you are creating your dates correctly.

Show us the relevant ASP code if you are still having problems. I hope this makes sense.

EDIT:

I just noticed that you wrote this:

quote:

I have an asp page with a hidden field that holds <%=Date()%>. Because the Session.LCID is set to UK this value today would be 12/03/2007 (UK format).

That is wrong way to go about it. Your web server is setting the date and outputting it in a particular format to the client, as a string in a hidden field. Then, that hidden field is posted to another ASP page which reads the field (which is just a string remember) and then it tries to take that value and convert it BACK to a date to pass to a stored procedure. This introduces formatting and conversion issues. Since your asp code alreadys knows the date, just set it directly in ASP and don't use the string value posted back by your web page. i.e.,

YourCommand.Parameters.Add(@Date, Date())

my syntax is probably off since I don't use non-.NET ASP much these days, but hopefully you get the idea.

- Jeff
http://weblogs.sqlteam.com/JeffS

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 09:34:40

Thanks Jeff,

So you are saying that I need to create the <%=Date()%> directly in my ASP command, as opposed to creating it a couple of pages back and passing it in that way? Have I got the right angle on this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-12 : 09:45:05

Yes.

You still might need to output the date to your web page for whatever reason, but in terms of the database, just set a parameter equal to Date() and you are fine.

If you do need to accept any dates from anywhere else (i.e., user input on a web page), you must validate it and convert it to a valid date in ASP first, and then you can pass that valid date to SQL Server as a parameter.

- Jeff
http://weblogs.sqlteam.com/JeffS

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-03-12 : 09:51:43

That works perfectly. Thankyou very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources