Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-14 : 22:35:02
|
Leighton writes "I am coding some ASP that uses date and time stored in SQL server 7 as smalldatetime.
The problem I have is I am trying to move the code and database to a live site and the format of the smalldatetime is handled differently in the two installations.
The format which works with the ASP code does this;
UPDATING or INSERTING - string has to be MMDDYYYY_HH:MI:SS. If MM or DD are single digit SQL server (I assume) will prefix them with a 0 (so the date portion of the string is always a constant length) and if the time portion is left out or actioned as 0:0:0 it will not show when displayed in the 'return all rows of table' panel.
SELECTING - string returned will always be DDMMYYYY or DDMMYYYY_HH:MI:SS format (note DD and MM reversed from how INSERT or UPDATE format is required).
However, on the other machine (which is NT as opposed to W98), things are different;
DD and MM values are reversed.
If DD and MM are only 1 character long they are not padded out with a zero.
For a start this throws out all the postional string manipulation causing the ASP to go wrong. Also there are additional problems displaying the time portion of the field (ie controling when it does and doesn't appear is handled automatically in the first scenario.
I believe it is to do with local settings or something, perhaps in conjunction with the ODBC settings but I have been looking over all the help files I can find and can't find anything about setting the default way datetime will be handled.
I know when looking at the data via query analyzer they both return the data in the format YYYY-MM-DD HH:MI:SS (which is different again!).
In the first scenario when you display all rows of the relevant table (when in table view) it shows as DD/MM/YY HH:MI:SS (with the HH:MI:SS not defaulting to 00:00:00 if it hadn't been supplied). I am not sure what happens in the second scenario when displaying the rows via table view - problems with remote server.
I am getting really stumped so any thoughts would be greatly appreciated.
Cheers,
Leighton." |
|
|
|
|
|
Setting default smalldatetime style for SQL server database