Unwanted conversion of date

[bostjank]

Hi!

I have encountered a strange problem that I can't find solution to - when I enter a date it is automatically converted to some other format when entered into database.

Details:

If i put data into database using this SQL

sSQL = "INSERT INTO T_MEMBERS(Name, DateMember) VALUES('Peter','8.1.2004')"
where 8.1.2004 is a date in d.m.yyyy format. The data in the database are:

Name = Peter
DateMember = 1.8.2004 >> m.d.yyyy

DateMember datatype is "SmallDateTime"


I have an SQL database on Windows 2003 Server (IIS 6). The strange thing is thet the server's configuration is the same as configuration on thest machine, where dates are saved correctly. Also, the database on the server was restored from the database on test machine, so their structure is the same (and collation is the same too).

Furthermore regional settings are correct on the server and the date is displayed correctly

Response.Write Date returns 8.1.2004 for today.

Any ideas what could be the problem?
Thanks very much.

Bostjan

[raf]

ASP always puts dates into mm/dd/yyyy, regardless of what format you use to actually display them.

For Access or MsServer, you can specify an input mask for the entered values.
But i never found a bulletproof, simple and universal method to deal with the way Microsoft implements dates...

[bostjank]

This is the first time I see this automatic conversion. And the dates are not noverted on the test machine.

[Caffeine]

I had a similar problem where IIS randomly switched formats of the time, I ended up writing my own function that gives me the time the way I want it. I already had this kind of function for dates.

[raf]

Microsoft realy did some funy stuff there which makes it hard for dd/mm/yyyy format users to use date inside code. Because itindeed depends on serversettings what date you might end up with. I suppose that writing your own function is probably the only sollution, but even then ... You should get the number of days from the unix epoch or so to be safe.

I never had a problem with dates in PHP - Apache - whatever db
I then sometimes need to use a stringfunction or so but at least, i'm sure that what i see on screen, can be saved as such.

[bostjank]

I could write my own function, but I don't know what would that help, as I do not convert dates at any time

I simply put the date (entered in a form) to SQL statement (where the date is displayed correctly).

So the format changes somewhere between executing query and insertion into database.

[raf]

I simply put the date (entered in a form) to SQL statement (where the date is displayed correctly).
There is no way you can see the query that IIS sends to your db. You can print the value(it's just a regular string like any other) of variale that contains your sql-statement, but that is not necessarely the same as IIS sends on. You could have a printed date in your sql-string like 19/01/2004 while the underlying ASP date is 01/19/2004.

If you write your own function, then you could keep the date as a regular string and insert it as a string inside the sql-string. This way, it will not get converted into a (wrong) date. If you then need to use a datefunction for your 'date-as-sting-value' then you can first use dateserial() and regardless of how ASP treats the datevalue then, you will always have the intended date (since you specify the year, month and day yourself). And the month() or day() or year() function on the created date will always be correct.
So you would be able to use them to transform the date back into a 'date as string' value.

[bostjank]

I'm really sory, but I don't know how that would help - the date in the SQL string is already in string format (I'm not using any format date..) and is not correctly saved in SQL.

I mean, the date in SQL for is saved as 1.8.2004 and is interpreted as August 1st 2004. And when I read the data from ASP it is also understood as August 1st.

I guess I could use nvarchar datatype instead of smalldatetime, but then I cannot sort the records by date.

[A1ien51]

What everyone here is trying to say is this:

IF you want your date to show up the correct way on the screen after you pull the information out of the database, you must write a function that converts it to the format you want

basic idea is to rip apart the access date and place it back togetherin the correct format.

Also the date is not in string format when you are using type of smalldatetime. Using that will convert it as everyone has said.

Eric

[bostjank]

That is the problem - I do not just need to show the correct date, but the date in the database must be valid - basically the date in SQL is in dd.mm.yyyy format, but before insertion it somehow gets coverted so that mm.dd.yyyy is written.

That of course means that SQL inteprets date as dd.mm.yyyy. - for example if I try to manually put the date in the database, I get an error if I write 28.12.2003)

[Caffeine]

Now I'm all confused, excuse me if this does not make much sense, I have re-written it 3 times now.

I just re-read your last post again and I'm not sure I'm following you.

You wrote that if you enter 28.12.2003 in the db you get an errormessage. What does it say ?
I would guess that the datatype in the db is set to some kind of date/smalldate or whatever they are called. If the field would be text [varchar, varchar2 etc] that value is supposed to be accepted as long as it does not contain more characters than what is allowed.

Are you absolutely 100% sure the column is set to accept text and not date/smalldate/timestamp/anything else ?

I've only worked with Oracle the past year so I'm not sure this would work for the other databases, but it might
Open the database-concole, and type in
desc <<YOUR_TABLE_HERE>>

and look what the datatype of the field is set to.

It's the only thing I can think of at the moment.

[raf]

this all is not making much sense to me.
if I try to manually put the date in the database, I get an error if I write 28.12.2003)
This means that your regional setting treats dates as mm/dd/yyyy, or that the . is not a valid seperator for your settings. Access will show the dates according to the regional settings but that has nothing to do with the underlying internal values, or with the format they are delivered in.
If you set an imput mask to specify that the values will be delivered as dd/mm/yyyy, then access will accept 28/12/2003 but will display it as 12/28/2003 if your regional settings are mm/dd/yyyy. The underlying value will still be 28/12/2003 though

What the actual value is, and what the displayed values is, doesn't allways need to be the same. If you choose a long-date format, then the displayed values will be differnt, but the underlying value also stay the same.

[Caffeine]

Hmm, ok. I've re-read [again] and this is how I see it: You have a datecolumn in the database of the datatype SmallDateTime.
It is always stored like this: m.d.yyyy
You want it to be saved like this: d.m.yyyy
If it is always stored like that there would be no problem, as long as the pattern is consistant.

edit:Just as raf wrote: [i]"What the actual value is, and what the displayed values is, doesn't allways need to be the same"

It seems tho as it is very important for you to have the date saved the way you want it. I'm thinking there might be a setting somewhere... if nobody can help you here, maybe you could google for something like "saving dates SQL Server"

edit: edited some stuff out as I missed it from post 1. This is not my day.

[Roy Sinclair]

Within the database datetime fields are normally stored as the number of milliseconds since some arbitrary (but constant) point in time. How it's displayed in ASP depends upon the "Regional Settings" for the user the ASP page runs under (the IUSR_* account). If you want the output from SQL to be formatted exactly one way then use the convert function in SQL to cause the datetime field to be output from the database formatted the way you want it.

To handle the input problems though you really should set the regional settings for that account to match the user's expected input.

[bostjank]

Regional settings for date are dd.mm.yyyy

In my previous mail I made a mistake - there is no error if I write 28.12.2003, but there is error if I write 12.28.2003 manually - so the date format in SQL is correcct.

This is the error i get if I try to enter 12.28.2003:

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

/inet/dbsave.asp, line 69

[Roy Sinclair]

The error you're getting is consistent with your regional settings.

When you enter 12.28.2003 you're saying the 12th day of the 28th month of year 2003 which is obviously invalid.

If you want to accept the date from the users in multiple formats you'll have to code the checking and conversion to the expected regional date format yourself instead of trying to rely on the built in conversion.

[bostjank]

Yes, I know it's consistent - the problem is only that the date automatically converts from dd.mm.yyyy (as it is written in SQL INSERT statement) to mm.dd.yyyy somewhere before the date is actually inserted in the database.

I could of course put the mm.dd.yyyy date in SQL statement (the date would then be converted to dd.mm.yyyy which is accurate), but the problem occurs only on 1 machine and the code must be the same on all machines (replication)