March 23, 2011 at 3:19 am
Asan Buhari (3/22/2011)
I wondered how SQL Server accepted an extra comma at the end of Create Table statement!
No-one seems to have addressed this yet?
Undocument feature, or bug?
March 23, 2011 at 4:30 am
create table D1 (a int,df varchar(20),)
Note: Nothing to worry about it.
Bcoz aftet df varchar(20) declaration , its considering as we are going to declare one more variable. but we didnt, so it will discard that.
so it could be possible one.
try with sqlserver2005
March 24, 2011 at 7:25 am
i got it wrong.... anyway that a nice question..
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
March 24, 2011 at 7:46 am
Toreador (3/23/2011)
Asan Buhari (3/22/2011)
I wondered how SQL Server accepted an extra comma at the end of Create Table statement!No-one seems to have addressed this yet?
Undocument feature, or bug?
I tested the identical script in both 2005 and 2008 and neither version reported an error. In my opinion is it is so trivial that it can not be classified as a bug, nor is it worth any of Microsofts SQL server staffs time to go back and alter the offending parsing routine.
March 24, 2011 at 8:18 am
bitbucket-25253 (3/24/2011)
In my opinion is it is so trivial that it can not be classified as a bug
Just because it's trivial doesn't mean it's not a bug - it's just a bug that nobody is ever going to do anything about 🙂
March 26, 2011 at 11:17 am
Toreador (3/24/2011)
bitbucket-25253 (3/24/2011)
In my opinion is it is so trivial that it can not be classified as a bugJust because it's trivial doesn't mean it's not a bug - it's just a bug that nobody is ever going to do anything about 🙂
But in this case it's not a bug, it's a feature 😀 . At least that's what I'll tell MS if they ask me (which is, however, fairly unlikely).
It makes it easier to comment out bits of the table definition without altering layout (see Hugo's comment on this). Without the trailing comma you could have a problem.
Anything that makes playing with the design easier is a good thing (as long as the playing is done on the appropriate systems).
Tom
March 26, 2011 at 11:45 am
rmechaber (3/22/2011)
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:http://www.karaszi.com/SQLServer/info_datetime.asp.
A salient quote:
Warnings and common misconceptions
Let me say this again: you don't want to use a combination of format and type which isn't language neutral unless you make sure that you have the correct DATEFORMAT and LANGUAGE settings in your applications.
The Numeric format can use dash (-), dot (.) or slash (/) as separator. The rules for how SQL Server parses the string doesn't change depending on the separator. A common misconception is that the ANSI SQL format (sometime a bit incorrectly referred to as the "ISO format"), 1998-02-23, is language neutral for smalldatetime and datetime. It isn't. It is a numeric format and hence it is dependent on the SET DATEFORMAT and SET LANGUAGE setting:
SET LANGUAGE us_english
SELECT CAST('2003-02-28' AS datetime)
-----------------------
2003-02-28 00:00:00.000
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.
I was surprised by this behavior ("behaviour?"), b/c I thought yyyy-mm-dd was universally acceptable as a date to most modern RDMS. It isn't. Not always.
So even avoiding the more obviously ambiguous format of '02/28/2008' vs. '28/02/2008', it can be less than straightforward storing dates in SQL Server.
Rich (U.S.-based DBA)
Interesting. All the servers I had worked fine with yyyy-mm-dd format when set to UK English, and also when set to US English.
Some of them got set to US English by mistake and changed; when they changed the mm/dd/yy format stopped workin and dd/mm/yy started working; but canonical ODBC format (YYYY-MM-DD) worked both before the change and after.
So my experience directly contradicts the article.
Could this be a change that MS introduced some time after SQL 2000 (that's where all my real heavyweight experience has been, I've been much less involved with 2008, and 2005 I hardly ever touched)? Well, if it were that would be really remarkable (and, for me, quite unbelievable; but I'm without any SQL Server platform until some time in May so I can't verify).
It's also interesting that SQL Server has no conversion style that will deliver YYYY-DD-MM (or YY-DD-MM) format from datetime using CONVERT. The styles that begin YY (11, 111, 12, 112, 20, 120, 21, 121, 126 and 127) all use YMD order. See Cast and Convert in Bol; and I find it impossible to believe that MS would fail to provide a simple means of generating the expected input format.
The format usually referred to as the ISO format, incidentally, is YYYYMMDD or YYMMDD - no separators (styles 112 and 12); there are other ISO formats as well (styles 126 and 127); I never use any of these unless I have to specify the zone, ODBC canonical works for me.
Tom
March 27, 2011 at 11:23 am
Tom.Thomson (3/26/2011)
rmechaber (3/22/2011)
A fascinating and scary exploration of how SQL Server doesn't always follow ANSI syntax for dates:http://www.karaszi.com/SQLServer/info_datetime.asp.
A salient quote:
Warnings and common misconceptions
Let me say this again: you don't want to use a combination of format and type which isn't language neutral unless you make sure that you have the correct DATEFORMAT and LANGUAGE settings in your applications.
The Numeric format can use dash (-), dot (.) or slash (/) as separator. The rules for how SQL Server parses the string doesn't change depending on the separator. A common misconception is that the ANSI SQL format (sometime a bit incorrectly referred to as the "ISO format"), 1998-02-23, is language neutral for smalldatetime and datetime. It isn't. It is a numeric format and hence it is dependent on the SET DATEFORMAT and SET LANGUAGE setting:
SET LANGUAGE us_english
SELECT CAST('2003-02-28' AS datetime)
-----------------------
2003-02-28 00:00:00.000
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.
I was surprised by this behavior ("behaviour?"), b/c I thought yyyy-mm-dd was universally acceptable as a date to most modern RDMS. It isn't. Not always.
So even avoiding the more obviously ambiguous format of '02/28/2008' vs. '28/02/2008', it can be less than straightforward storing dates in SQL Server.
Rich (U.S.-based DBA)
Interesting. All the servers I had worked fine with yyyy-mm-dd format when set to UK English, and also when set to US English.
Some of them got set to US English by mistake and changed; when they changed the mm/dd/yy format stopped workin and dd/mm/yy started working; but canonical ODBC format (YYYY-MM-DD) worked both before the change and after.
So my experience directly contradicts the article.
Could this be a change that MS introduced some time after SQL 2000 (that's where all my real heavyweight experience has been, I've been much less involved with 2008, and 2005 I hardly ever touched)? Well, if it were that would be really remarkable (and, for me, quite unbelievable; but I'm without any SQL Server platform until some time in May so I can't verify).
It's also interesting that SQL Server has no conversion style that will deliver YYYY-DD-MM (or YY-DD-MM) format from datetime using CONVERT. The styles that begin YY (11, 111, 12, 112, 20, 120, 21, 121, 126 and 127) all use YMD order. See Cast and Convert in Bol; and I find it impossible to believe that MS would fail to provide a simple means of generating the expected input format.
The format usually referred to as the ISO format, incidentally, is YYYYMMDD or YYMMDD - no separators (styles 112 and 12); there are other ISO formats as well (styles 126 and 127); I never use any of these unless I have to specify the zone, ODBC canonical works for me.
Maybe someone with a 2000 server could check this for us?
I did some playing around with this. Each login has its own language setting that overrides the language setting set for the server. I'm pretty sure that if you create a user on a server set to British English (so login is set to British), then change the server's language setting, that login/user retains its originally created default language setting. So you may have created logins and then changed the server language without affecting user settings?
To me, it's goofy that 'yyyy-mm-dd' is not a universally acceptable and unambiguous date format (only 'yyyymmdd' is), but that seems to be the case.
Rich
March 28, 2011 at 4:06 am
rmechaber (3/27/2011)
To me, it's goofy that 'yyyy-mm-dd' is not a universally acceptable and unambiguous date format (only 'yyyymmdd' is), but that seems to be the case.
It is goofy, indeed. Especially since yyyy-mm-dd is one of the ISO standard notations. As far as I know (can't test it at the moment), SQL Server will work reliably when converting yyyy-mm-dd to the new date and datetime2 formats, but the old behaviour for conversion to datetime has not (yet?) been changed in the newer versions.
March 28, 2011 at 5:54 am
Hugo Kornelis (3/28/2011)
rmechaber (3/27/2011)
To me, it's goofy that 'yyyy-mm-dd' is not a universally acceptable and unambiguous date format (only 'yyyymmdd' is), but that seems to be the case.It is goofy, indeed. Especially since yyyy-mm-dd is one of the ISO standard notations. As far as I know (can't test it at the moment), SQL Server will work reliably when converting yyyy-mm-dd to the new date and datetime2 formats, but the old behaviour for conversion to datetime has not (yet?) been changed in the newer versions.
Executing in 2008 Developer Edition
SELECT CAST('2011-04-28' AS DATE), CAST('2011-04-28' AS DATETIME2), CAST('2011-04-28' AS DATETIME)
Result:
2011-04-28 2011-04-28 00:00:00.0000000 2011-04-28 00:00:00.000
Executing in 2005 Developer edition
SELECT CAST('2011-04-28' AS DATETIME)
Result:
2011-04-28 00:00:00.000
March 28, 2011 at 6:03 am
bitbucket-25253 (3/28/2011)
Hugo Kornelis (3/28/2011)
rmechaber (3/27/2011)
To me, it's goofy that 'yyyy-mm-dd' is not a universally acceptable and unambiguous date format (only 'yyyymmdd' is), but that seems to be the case.It is goofy, indeed. Especially since yyyy-mm-dd is one of the ISO standard notations. As far as I know (can't test it at the moment), SQL Server will work reliably when converting yyyy-mm-dd to the new date and datetime2 formats, but the old behaviour for conversion to datetime has not (yet?) been changed in the newer versions.
Executing in 2008 Developer Edition
SELECT CAST('2011-04-28' AS DATE), CAST('2011-04-28' AS DATETIME2), CAST('2011-04-28' AS DATETIME)
Result:
2011-04-28 2011-04-28 00:00:00.0000000 2011-04-28 00:00:00.000
Executing in 2005 Developer edition
SELECT CAST('2011-04-28' AS DATETIME)
Result:
2011-04-28 00:00:00.000
I think you missed first changing the language, Ron? Execute this first and try it:
SET LANGUAGE british;
Hugo, I don't have access to SQL 2008 right now, but Tibor Karaszi's article that I linked to indicates that the new 2008 data types of DATE, TIME, and DATETIME2 are fine with ANSI syntax ('20011-04-28'), but that neither DATETIME not SMALLDATETIME are compliant.
I'd still like to see someone try this out on a SQL 2000 box:
SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Rich (SQL 2005 Enterprise ed.)
March 28, 2011 at 6:25 am
rmechaber (3/28/2011)
I'd still like to see someone try this out on a SQL 2000 box:
SET LANGUAGE britishSELECT CAST('2003-02-28' AS datetime)
Similar error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
And that's whether I use Query Analyser on the local machine or SSMS remotely.
March 28, 2011 at 6:28 am
Tom.Thomson (3/26/2011)
It makes it easier to comment out bits of the table definition without altering layout (see Hugo's comment on this). Without the trailing comma you could have a problem.
On that basis, is it a bug that the same thing won't work with a Select statement 😉
March 28, 2011 at 6:31 am
Duncan Pryde (3/28/2011)
rmechaber (3/28/2011)
I'd still like to see someone try this out on a SQL 2000 box:
SET LANGUAGE britishSELECT CAST('2003-02-28' AS datetime)
Similar error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
And that's whether I use Query Analyser on the local machine or SSMS remotely.
Thanks very much Duncan, that is what I suspected would result.
Tom Thomson: it would be interesting if you still have access to those databases to poke around and see what kind of results you get. You may find that there are per-user or per-query language settings that are different from the server settings. In any case, this confirms that the DATE data type is not language-independent for the ANSI/ISO syntax of 'yyyy-mm-dd'.
Rich
March 28, 2011 at 6:57 am
rmechaber (3/28/2011)
Thanks very much Duncan, that is what I suspected would result.Tom Thomson: it would be interesting if you still have access to those databases to poke around and see what kind of results you get. You may find that there are per-user or per-query language settings that are different from the server settings. In any case, this confirms that the DATE data type is not language-independent for the ANSI/ISO syntax of 'yyyy-mm-dd'.
Rich
I had always used the ANSI syntax thinking it was multilanguage, only to discover later that it wasn't. The reason I'd never noticed is probably that almost all our servers are set to US_English, even though we're in the UK. I knew the 'dd-MM-yyyy' format was not multilanguage so had never used it.
Interestingly, I've changed my SQL 2000 box to default to British English using the properties box in Enterprise Manager, but when I open a new connection to the server in QA or SSMS and run "SELECT @@LANGUAGE" it always returns "us_english". I can't find an option in QA or SSMS that would override the server setting, so I'm a bit mystified.
Viewing 15 posts - 76 through 90 (of 101 total)
You must be logged in to reply to this topic. Login to reply