SQL Server Performance Forum – Threads Archive
dates being switched when saved – SOLVED
I’ve been having a stupid problem that not only can I not replicate easily (because it’s on a clients machine) but I can’t find the exact scenario anywhere online. The problem is this: I have a client running an ASP web app that the company I’m with has designed and marketed. We run it in-house off a Windows 2000 server using a MS SQL Server database. Everything works exactly the way it wants (at least regarding this issue). Only in the clients environment are there issues. What’s the issue you ask? As the subject line states, the dates are being switched. This is the stupid part.[?]
When the client first creates a record, there’s no problem – all the data is saved exactly the way it’s supposed to. The dates are saved in the mm/dd/yy format – which the app is built to use. BUT, when they then edit that record – the date is displayed correctly UNLESS they save it. If they save that record (using EXACTLY the same SQL statements, the date gets switched. So 11/25/04 gets saved, or attempted to be saved as 25/11/04 which of course fails. 11/09/04 is turned to 09/11/04. As you can guess this is NOT what it’s supposed to do and it’s causing big issues. The problem is I can’t replicate that error in my environment at all – and to make it stupider – in their environment, they have a couple WinXP, a couple NT, a 98 AND a couple Server 2000. The XP systems have no problem with the dates. All the rest do. I’ve doubled checked regional settings, I’ve tested and retested the code, nothing is fixing this let alone telling me what’s wrong. Can anyone help or at least point me in the right direction? Thank you, at this point, any help at all is appreciated. Matthew Email: [email protected]
I cant really point to you where this is happening, but you may consider a different date format for the app to use. The one that seems to work accurately for any regional/system etc setting is YYYYMMDD I’m assuming the field to store the date is a datetime field since you wouldnt get an error otherwise. Is the date field changed when they save? If its not a changeable field, then dont save it during an update. Sorry cant offer more. You’ve checked the things I would have started with.
I’ve tried different date formats and it doesn’t seem to make any kind of difference – it just automatically flips the month and day values – and it will keep flipping them every time its saved. There are 3 date fields (each one different) and they are all editable. I’m totally at my wits end with it.
Run SQL Profiler on the client site to see if the date is flipped before it is submitted to SQL. If so then you’re gonna have to do some debugging in the asp code firstly in the post back to the server. Make sure that when the server receives the info it is still in the same format
secondly in the page that prepares the post back info echo out the date before it posts it Cheers
Twan
Assuming a couple of things … (1) The ASP app handles inserts and updates in separate procedures.
(2) The date is not entered in a calendar control, but in a "date text" field.
(3) In ‘old’ ASP you could not type variables, so they’re always a Variant. IME, putting a date value in a Variant will often cause a switch between day and month, unless the day is >12. (Also, there is no problem if the regional date setting for the Windows session running the app is mm/dd/yyyy.) … I would hazard a guess that the update procedure uses a variable to pass on the new date value. You need to force the format of that new date value to yyyy-mm-dd when feeding it to the variable, or work around this.
Thank you everyone for your suggestions. I still don’t have a resolution but there’s 2 things I think I can try. I have tried different formats and it doesn’t make a difference. I ran a trace on it as suggested and the data is being switched at the SQL level – I’ve displayed there variable that gets set to the database field and it is in the correct format. Ie: I retrieve the data, 12/03/2004 (dec 3, 2004), it gets set to the variable correctly, I’ve formatDateTime() just in case and it’s still correct (as well I’ve double checked the regional settings for short date) then I set the fields to the dates retreived and when I send an "update" it switched the values. It does this every single time I save it – unless the day is greater then 12 as pointed out. The two things I can think of is to switch the format to yyyy/mm/dd as suggested – or to the long date and then save that. The other is I thought I’d set up a seperate copy of the app and either a new instance of the database or simply another copy of the database and then physically go to the clients and try to work it out that way. Does anyone have other suggestions? Thanks again for all your help. Matthew
When I say yyyymmdd – dont include the / — this may continue the problem.
I’ve observed that if you keep the date as 29-Nov-2004 its generally kept in the same format. HTH Harsh
Matthew, Can you post the literal UPDATE statement that is being issued? Especially the expression where your problem date field is set.
Running the trace – this is what gets run:
exec sp_cursor 180150000, 33, 1, N’tblWorkOrder’, @fldWOStatusID = 1, @fldDate = ‘Nov 29 2004 12:00AM’, @fldCompanyID = 72, @fldBillToID = 72, @fldTechnicianID = 6, @fldManagerID = 5, @fldShortDesc = N’B.K.A.’, @fldWorkRequired = ”, @fldDateRequired = ‘Dec 3 2004 12:00AM’, @fldCurrentStatus = ”, @fldSiteStreet = N”, @fldSiteCity = N”, @fldSiteProvince = N”, @fldSitePostalCode = N”, @fldSiteContactName = N”, @fldSiteContactPhone = N”, @fldSiteContactFax = N”, @fldCustomerPONum = N”, @fldCustomerJobNum = N”, @fldNotes = ”, @fldMeasureDate = ‘Mar 12 2004 12:00AM’ As for the statement I’ve used: it’s just rs.fields("fldMeasureDate") = measuredate. I’ve formatted measuredate before that statment as well as part of it. I’ve displayed the value, before and after and it’s the correct format: mm/dd/yyyy. But as soon as the update statement is run (just rs.update) then the above is created. Like it was mentioned, if the day is greater than 12, it doesn’t flip but if the day is less then 12 it flips the day and month every single time. I haven’t tried removing the "/" but I didn’t think it would work that way? Don’t I need to use the date seperator, wouldn’t it take 12032004 as a literal number if I don’t use the /??
Okay, so you’re talking Visual Basic or something? * What variable type is MeasureDate?
* What field type is fldMeasureDate? Formatting MeasureDate may actually be what is causing the switching. Try feeding it the yyyy-mm-dd format, or use a Sting variable instead of a Variant – Variants invariably mess up dates, so unless you can be absolutely sure of the local settings for the client app … Test this app with different local settings! If you’re handling any kind of decimal values, don’t forget to test with different decimal separators as well. Things can get really ugly out in the real world.
I’ve tried it both formating measuredate and not, the same thing happens. I’m using ASP (not ASP .NET) so I can’t declare a variable type, I’m stuck with the variant. I’ll try chaning it to yyyy/mm/dd – but will sql convert it too?
IIRC the / date separator was causing problems as well, so use a regular hyphen (-) instead. A sneaky work-around is to add a prefix to the date string, like so: MeasureDate = "DT:2004-12-01" This makes sure that the information is treated like a text string, and the date will not get mangled. But then you also have to use a substring function – e.g. Mid() – whenever you need to get the actual date: rs.Fields("fldMeasureDate")= Mid(MeasureDate, 4, 10)
As Harsh mentioned above, the date formats 29-Nov-2004 (although not used this one myself)
and
29 Nov 2004 tend not to get mangled. SELECT CONVERT(varchar(11), getdate(), 106) will yield the second format in SQL server, unfortunately I don’t know about ASP but in VBA ‘ declare your variable
Dim cStartdate as Date Format(cStartDate, "dd mmm yyyy")
Will sort out the date format at the VBA end, is there something similar in ASP? Regards, Robert. P.S. CONVERT will also help to get rid of the time parts in a date. PPS Obviously if you have different language settings on the server and client as well as regional settings you may get problems with this approach.
Solved it! Thank you to everyone who had suggestions. The solution I took was to switch the date after I retrieved it into yyyy/12/3 (yyyy/mm/dd) format and save that and it fixed the problem. I did it like this: rs("date") = year(varDate) & "/" & month(varDate) & "/" day(varDate) Thanks again everyone for your help! Matthew
Robert, Matthew’s problem was that he’s working in ‘old’ ASP, which doesn’t have any specific variable types, only the Variant type. One issue that I always run into with the Date type in VBA is that it doesn’t accept Null (and throws an ugly error, IIRC). Once you declare a variable as Date, you have to write parallel code for what to do if you get hit by a Null. There are many computers out there that do not use US regional settings, so there’s no point in ignoring a fact of life. Also I wouldn’t count on Format() without some thorough testing with many different date settings. German and Italian are good in that they have the "." date separator and also significantly different names for months. There was a peculiar problem with Windows NT 4.0 for Workstations (which is of course no longer with us): when the setting for Short Date on the regional settings had "mm", it would always do the switch if the day of the month entered was less than 13. No other way around that than to force people to change the Short Date setting to use "mmm" or "mmmm".
]]>