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 |
|
jones_d
Yak Posting Veteran
61 Posts |
 Posted - 2005-01-14 : 06:11:40
|
| Hi,I have a table, which stores a date field in the following format:2002-12-27 00:00:00.000However, If I want to select a record with the above date I have to use the following format in Query Analyser:where cur_trn_dt = '2002-27-12 00:00:00.000'The database concerned was upgraded from SQL 7.0 to SQL 2000 and I don't have any problem in the SQL 7.0 database. Is there a database date setting that might be affecting the query in Query Analyser?Any ideas?Thanks, |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-14 : 08:56:49
|
| set dateformat ymd... at the start of each session... |
 |
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-14 : 09:29:13
|
| Thanks Stoad.Is there anyway that I can set this up as the default date format in QA?I would like to avoid having to set it at the beginning of each session if possible.Thanks |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-14 : 13:30:51
|
Sorry if I'm wrong but I think you/ we can't. I think this option has to be done during SS installation, along with (or as a part of) server language, server codepage etc.Also, I think that this option has nothing in common with QA. When you write in QA:where cur_trn_dt = '2002-27-12 00:00:00.000'how it (QA) can know that column cur_trn_dt has datetime data type? |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-17 : 05:09:49
|
| Hi Stoad,QA should know that cur_trn_dt has a datetime data type because it is a column in an existing table. The data type of this column is datetime. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-17 : 05:30:55
|
| QA is just a nice client tool. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-01-18 : 16:35:16
|
| Well, Jones, you're running into internationalization errors here and that is why many people prefer to always refer to dates in ISO format which is YYYYMMDD. You see, the database is not really storing the datetime data in '2002-12-27 00:00:00.000' format but rather it is stored as two 4-byte integers that evaluates to the number of days and milliseconds after midnight (for the time portion) before or after January 1, 1900.Your default display mode is telling it to show as that string. In order to accurately select and compare datetime values, SQL Server attempts to convert any strings to datetime values based on system default settings. In order to make sure that this is never a problem, many developers resort to always putting their date values into YYYYMMDD format which is always interpreted correctly. So you would do where cur_trn_dt = '20021227'Note that if the time portion is left off, it is assumed to be '00:00:00.000'.---------------------------------------------------------------------------------Infoneering: Information Technology solutions engineered to professional standards. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-19 : 03:31:26
|
| >SQL Server attempts to convert any strings to datetime values>based on system default settings.better:based on default_language assigned to the current login (on behalf of whicha SQL statement is to be executed).EXEC sp_addlogin 'john', 'sunrise', 'pubs', 'us_modern_greek'The other thing that very often this 4th parameter is not specified explicitlyand for this reason login language implicitly is set to server default language.Also worth noting, the login languages are meant only for setting logins'DATE FORMAT style, DATE FIRST day of week and MONTH (short)names. |
|
|
|
|
|
|
|
|
Date Format in Query Analyser