January 19, 2004 at 11:43 am
Hello,
My name is Don.
I am new to this board, if this post is in wrong section I apologize. I need some help..........
Background: running SQL Server 2000, on Win Server 2000 with latest service packs installed.
The script/database in question ran without the problem described below for over 2 years.
Problem started when database was moved to a new server that had been mistakenly setup with OS date formatted as dd/mm/yyyy.
It was subsequently changed to mm/dd/yyyy.
This is my problem:
The script (VBScript/ASP) loads a date value, example '01/15/2004' (created as Date()) into a data type datetime column.
The problem is the date is stored as dd/mm/yyyy not mm/dd/yyy (the current default OS format).
However if the date is not valid as dd/mm/yyyy it is stored as mm/dd/yyyy (assuming it is valid date)
For example: value = '01/10/2004' will be stored as '10/01/2004' but value = '01/20/2004' will be stored as '01/20/2004' (20 is not valid month).
We are not loading any time values into the datetime column (and did not on the old server).
I have tried inserting record using ADO and executing a direct insert statement with same results.
Just to confirm, database specs and scripts ran for long time on original server ok, the problem surfaced when my customer moved the database to a new machine that had incorrect OS date format at the time SQL Server and the database in question was installed.
Any thoughts on how we can fix this?
Thanks for helping.
PS I can't seem to figure out how to enter line breaks, the enter key keeps submitting this form!!!
January 19, 2004 at 3:45 pm
use set dateformat to specify date format before loading
dateformat could have mdy, ydm or ymd. Check book-on-line for detail.
January 20, 2004 at 7:05 am
Check language setting for login on new server. This can affect date input.
Far away is close at hand in the images of elsewhere.
Anon.
January 20, 2004 at 8:53 pm
Hi,
January 20, 2004 at 10:45 pm
How about not using the datetime type. Could also send from Database... and using JavaScript (like Date Picker) to do all the formatting.....
January 21, 2004 at 9:29 am
Thanks for the suggestions.
We have set formatting and language correctly.
Just to confirm, we are running server side VBScript scripts.
I should have posted snippets of the code in question. Here it is:
[first we set the date]
vDate = Date()
vFrom = Month(vDate) &"/1/"& Year(vDate)
vTo = Month(vDate)&"/10/"& Year(vDate)
vFrom = FormatDateTime(vFrom,2)
vTo = FormatDateTime(vTo,2)
Response.Write "vFrom = "& vFrom & " and vTo = " & vTo (used for debugging, both dates display correctly)
[then we load the table with recordset.update using ADO object]
transRS("FromDate") = vFrom [transRS("FromDate") is Datetime datatype]
transRS("ToDate") = vTo [transRS("ToDate") is Datetime datatype]
transRS.Update
Using above example the record will be stored with ToDate as 10/1/2004
[In an effort to to get fix the problem we also run a routine and end of script, it does not fix the date format]
Sub FixDates
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open vConn [this is the connection string]
dateSQL = "Update BillingBatchControl Set FromDate = '"& vFrom&"' Where BatchNum = "& vBatchNum
Conn.Execute dateSQL
dateSQL = "Update BillingBatchControl Set ToDate = '"& vTo&"' Where BatchNum = "& vBatchNum
Conn.Execute dateSQL
dateSQL = "Update BillingSummary Set ToDate = '"& vTo&"' Where BatchNum = "& vBatchNum
Conn.Execute dateSQL
dateSQL = "Update BillingSummary Set FromDate = '"& vFrom&"' Where BatchNum = "& vBatchNum
Conn.Execute dateSQL
Conn.Close
End Sub
January 21, 2004 at 2:35 pm
Can you confirm if the dates are stored correctly in the table or not. That is when you SELECT BatchNum, FromDate FROM BillingSummary do the dates match properly?
If not I assume you may need a one time fix to correct existing dates. This fix may have to use a convoluted conversion scheme (Can anyone think of a simpler method?) like (note I didn't add the time but it can be added if needed):
UPDATE BillingSummary
SET FromDate= CONVERT(datetime,CONVERT(varchar,DATEPART(DAY,FromDate)) + '/'
+ CONVERT(varchar,DATEPART(MONTH,FromDate)) + '/'
+ CONVERT(varchar,DATEPART(YEAR,FromDate)))WHERE .....-- some where clause to filter bad from good dates
Next I assume you have a VB problem with getting or reading the dates properly. That is VB assumes the date is 1/22/2004 and by the time SQL gets it the date is 22/1/2004. Have you tried the VB FORMAT function as in using
vDate = Format(Date(), "mm/dd/yy")
as opposed to
vDate = Date()
Use the FORMAT command when interpreting dates passed back from SQL.
Francis
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply