| Author |
Topic |
|
siti
Starting Member
11 Posts |
 Posted - 2005-07-11 : 03:27:15
|
| I have an application using asp that receive date input from user in the dd/mm/yyyy format. When I tried to insert the record into the SQL Server with an INSERT statement, I received an error message indicating the date index is out of range. i have changed my regional setting to Malaysia that using dd/mm/yy format but it doesn't work. I guess, the SQL Server is expecting the date format to be mm/dd/yyyy. Is there any way that I can input the date into SQL Server in dd/mm/yyyy format.help me. i need to complete my project immediately. thank you. |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-07-11 : 03:33:34
|
| If you lookup CONVERT in Books online (the SQL Server help files) you should find what you needsteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-11 : 03:44:28
|
| In your asp page do the formation 'dd-mmm-yyyy' and insert it into tableMadhivananFailing to plan is Planning to fail |
|
|
|
siti
Starting Member
11 Posts |
Posted - 2005-07-11 : 03:50:11
|
| can u teach me how to do the formation 'dd-mmm-yyyy' and |
|
|
|
siti
Starting Member
11 Posts |
Posted - 2005-07-11 : 03:52:51
|
| can we change the format in Enterprise manager. *sorry if my question look stupid to you. i'm still new with MS SQL |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-11 : 04:16:36
|
| Not sure whether you can use Format(date,"dd-mmm-yyyy") in ASPMadhivananFailing to plan is Planning to fail |
|
|
|
siti
Starting Member
11 Posts |
Posted - 2005-07-11 : 04:35:25
|
| i have read some examples but they are using the convert statement but i do not understand it. Is it a right way to do it? the datetime value in my application will be inserted in the: table: "student" attribute: "birth_date"how the syntax will be by using the convert statement?thank you |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-11 : 08:39:03
|
| even better. leave SQL SERVER alone. handle ALL FORMATTING on the CLIENT!!!/me cries*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-11 : 09:10:23
|
| Not only should you do formatting at the client, but your client should not "send" the data to SQL Server in any particular format either. It should be sending date VALUES not strings formatted to look like dates in certain formats. Always use stored procedures with properly datatyped parameters and formatting is never an issue, regardless of the settings of your server or client(s).Why do people like to make things more compliated than they need to be, and make more work for themselves????- Jeff |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-11 : 10:46:22
|
Job security? Make my life hell? both?  *need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
siti
Starting Member
11 Posts |
Posted - 2005-07-18 : 04:09:47
|
| if i use the convert statement to store date in MS SQL using dd/mm/yy format, should i use the same technics to view datetime data in dd/mm/yy format? |
|
|
|
siti
Starting Member
11 Posts |
Posted - 2005-07-18 : 04:28:45
|
| one more thing, should i set the datatype to varchar in MS SQL when using the convert statement |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-07-21 : 07:19:00
|
| Don't convert to varchar! SQL server will recognise most datetime formats - I try to use the ISO format (yyyymmdd). As Jeff suggested, the database doesn't care about the format. Could you give more detail on what you are trying to dosteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
kapilarya
Yak Posting Veteran
86 Posts |
Posted - 2005-07-21 : 07:42:31
|
| check this query if this can help select convert(datetime, '14/12/2005',103) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-21 : 08:30:21
|
| I repeat:Not only should you do formatting at the client, but your client should not "send" the data to SQL Server in any particular format either. It should be sending date VALUES not strings formatted to look like dates in certain formats. Always use stored procedures with properly datatyped parameters and formatting is never an issue, regardless of the settings of your server or client(s).- Jeff |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-21 : 10:30:26
|
| /me watches Jeff pound his forehead on the wall*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
|
change datetime format from mm/dd/yy to dd/mm/yy