| Author |
Topic |
|
rtown
Yak Posting Veteran
53 Posts |
 Posted - 2010-08-13 : 13:23:07
|
| This is s strange problem, but I have an INSERT into that requests data from a form. On the form I have the asp code for the current date which displays it in DD/MM/YYYY. However, the datetime format in sql only accept MM/DD/YYYY even though you can manually enter the other format in the table through access.I get the following error when I try to process the form in asp:[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.So im assuming this is the reason, because it worked yesterday, on the 12th when it probably assumed 12 was the month. Today is the 13th so it caused this error today since 13 is an invalid month. We do however want to have dates in the DD/MM/YYYY format so its sql that is at fault. How do I get it to accept dates in this format? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-13 : 13:37:24
|
| actually you should keep the date in the standard yyyymmdd format in SQL and show it however you want in your application.. and convert it back to yyyymmdd format when isnerting back into the database...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-13 : 13:42:27
|
| But I have no way of changeing the format of the ASP date code. It will always display in DD/MM/YYYY unless I change the regional setting of everyone using the database. So I guess my question is, if DD/MM/YYYY is submitted in the form, how do I make SQL recognize it as a valid date? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-13 : 13:43:50
|
| The format it is displayed as doesn't matter. Manipulate it ro yyyymmdd server side when the form is submitted, and before you pass it to the database |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-13 : 13:45:36
|
| How do I do this? And then to display the date in the table, how would I display it as ddmmyyyy? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-13 : 13:52:57
|
This is really an ASP question. Your ASP code should look a lot like this:Dim dtdt = Request.Form("dt")dt = Year(dt) * 10000 + Month(dt) * 100 + Day(dt) |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-13 : 13:56:22
|
| but the problem is not with ASP, it displays the date in the form just as we want it, the problem is sql wont accept it |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-13 : 14:00:25
|
| the problem is that you're passing an invalid date to the database. i'm not saying to display it differently, im saying to properly format it just b4 passing it to the database. |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-13 : 14:03:06
|
| this is what im asking how to do.as i said the only apparent way of doing this in asp is by changing the regional settings of the user, which obviously is retarded. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-13 : 14:04:14
|
something like this (be sure to add error handling and type checking)Dim dtDim sqlDim cmddt = Request.Form("dt")dt = Year(dt) * 10000 + Month(dt) * 100 + Day(dt)sql = "INSERT INTO Table1 (dt) VALUES('" & dt & "')"Dim cmdSet cmd = CreateObject("ADODB.Command")With cmd .ActiveConnection = YOUR_CONNECTION_STRING .CommandType = adCmdText .CommandText = sql .ExecuteEnd WithSet cmd = Nothing |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-13 : 14:07:38
|
| ill try that, seems complicated though. thank you. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
Datetime format doesnt match ASP date code format