February 18, 2003 at 7:04 am
I use sql 7, nt 4 server sp 6a, iis 4.0 sp 6, MDAC 2.5 sp2.
"insert into my_table (field1, my_date) Values ('"& text1 &"', '"& NOW() &"')"
I get an error message when trying to insert Now() using ASP (i.e. # 80040e07 - error converting char data type to datetime). If I use CDate it returns it is "not supported".
If I use getdate() as the default for datetime field in sql, everything is ok and I get the datetime - that is, regardless of date/time format in the regional settings date and time will be inserted and shown properly in sql tables (I would not try to insert Now() with asp page in this case of course).
When I use asp to insert Now() this is what happens:
a) If I change the system date so that the system returns valid numbers for BOTH Day and Month (e.g. 11/01/2003 or 09/10/2003 or 12/12/2003) the insert and update operation will be successful regardless of the format in regional settings.
b) If either system Day OR Month is more than 12 and I try to insert Now(), I get the above error message irrespective of how I try to do it and what is in the regional setting. Example:
When system setting is MMddyyyy hh:mm:ss and system date is Feb. 16, both 02/16/2003 and 16/02/2003 return error related "converting to datetime". I change the system setting to DDmmyyyy hh:mm:ss then reboot the machine and try to insert Now() again ...... I get the same error message for conversion.
I think Time is not an issue here, it's DD and MM.
Do I need to reinstall a component or add/upgrade something?
Thanks
February 18, 2003 at 1:06 pm
Check your users language settings. These do have bering on dates during inserts.
February 19, 2003 at 4:36 am
For the tests that I mentioned:
- I use IE6 which is installed on the same machine.
- In the regional settings locales I tried US and UK (both for DDmmyyyy and MMddyyyy) but the problem is still there.
Thanks
February 19, 2003 at 4:45 am
Sorry, I am not sure where you mean, sounds like machine regional settings is what you are saying. What I amm saying is the SL User account has a language setting. Open EM and drill thru security to the logins. Then open the properties of the User you are logging in with for tetsing and see what their language setting is. Change if needed then make sure all the connections for that user have logged off. When log back in with that user the new language will take effect.
Also, if still trouble see if SET DATEFORMAT will work for you. If that is still and issue then something odd is happening, I suggest Response.Write your built INSERT string to the web page to see what is being sent for sure.
February 21, 2003 at 5:02 pm
SQL user language was English all the time.
I did the DSN once again with British English and problem is solved now.
Thanks a lot for your help.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply