May 20, 2008 at 1:32 pm
mtassin (5/20/2008)
Lynn Pettis (5/20/2008)
I disagree with statement #2. SQL is a programming language. Yes, it is a Data Manipulation Language, but so is COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, VB.NET, et al. They all manipulate data is some way. How it does it is different.Name one application written in 100% SQL. There are a multitude of applications written in COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, Delphi, VB.Net. Some of them even store and retrieve data without SQL.
So, your definition of a programming language is that you have to be able to write a completely self contained application using only that language?
😎
May 20, 2008 at 1:49 pm
I think everyone is missing the point of what is happening.
What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
That being said, it would be nice if you could define the truncation setting for a parameter or procedure in the definition.
--
JimFive
May 20, 2008 at 2:02 pm
James Goodwin (5/20/2008)
I think everyone is missing the point of what is happening.What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
That being said, it would be nice if you could define the truncation setting for a parameter or procedure in the definition.
--
JimFive
Haven't missed it all. I agree with you.
😎
May 20, 2008 at 2:18 pm
mtassin (5/20/2008)
Lynn Pettis (5/20/2008)
I disagree with statement #2. SQL is a programming language. Yes, it is a Data Manipulation Language, but so is COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, VB.NET, et al. They all manipulate data is some way. How it does it is different.Name one application written in 100% SQL. There are a multitude of applications written in COBOL, FORTRAN, Pascal, Modula-2, Ada, C, C++, C#, Delphi, VB.Net. Some of them even store and retrieve data without SQL.
I don't have a commercially available program written completely in SQL, but I do have some custom applications for my own use that I run completely within Management Studio, using nothing but SQL Server commands.
One is a personal accounting application. Another I use for DMing AD&D, for tracking encounters and battles, exp and loot. (How uber-nerd is that? I myself shudder when I think about it. 🙂 ) I could throw a .NET front-end on either one, but I keep running into the "why bother" barrier on that.
(And next month, I have to rebuild the DMing one, since 4th Edition is coming out and sounds like a good improvement. The rebuild will be fun.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 2:26 pm
James Goodwin (5/20/2008)
I think everyone is missing the point of what is happening.What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
Here, here.
Same thing happens when converting decimal types with different accuracies.
If you are validating user input properly in the first place you won't have a problem. Don't expect SQL to catch everything for you, validate all user input.
May 20, 2008 at 2:33 pm
James Goodwin (5/20/2008)
I think everyone is missing the point of what is happening.What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
That being said, it would be nice if you could define the truncation setting for a parameter or procedure in the definition.
--
JimFive
Thanks, this is one of the best explanations I have read in this thread.
This case is interesting, too:
Declare @Tmp varchar(10)
set @Tmp = '20080520ABCDEF'
select CAST(@tmp as datetime)
In this case, the input can be converted to the parameter type, but the code still throws an error.
Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.
I suppose the bottom line is that the function parameter is basically just a suggestion, and the code of the function has to do the real work of handling the data properly.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2008 at 2:39 pm
GSquared -- Another I use for DMing AD&D, for tracking encounters and battles, exp and loot. (How uber-nerd is that? I myself shudder when I think about it. ) I could throw a .NET front-end on either one, but I keep running into the "why bother" barrier on that.
Now this is one I'd like to see!!
😎
May 20, 2008 at 2:40 pm
2. SQL isn't a programming language.
I'd have to dig out my Programming Languages text to see if SQL meets the definition, but I'm pretty sure it does. I vaguely remember that a programming language must have four constructs, incrementation, branching, comparison, and something else, unless the something else is pretty bizarre SQL probably has it.
I have written all sorts of programs in SQL. They are usually called Stored Procedures. It seems that you are basing this statement on the fact that SQL has no built in user/disk interface calls. Well, neither does standard Lisp (Common Lisp does, but technically they aren't necessary) and no one would argue that Lisp is not a programming language.
Pascal is a Procedural Language.
Lisp is a Functional Language.
SQL is a Declarative Language.
The only way you could argue that SQL is not a programming language would be to find a way in which it is not complete, (in the programming sense, which may not be exactly the same as Turing complete)
--
JimFive
May 20, 2008 at 3:15 pm
James Goodwin (5/20/2008)
webrunner,
Change that to varchar(8) and it will work.
'20080520AB' cannot be converted to a date, but '20080520' can.
--
JimFive
Right, but my point was that you won't always know whether the incoming data will work, for example:
Declare @Tmp varchar(8)
set @Tmp = 'A2008052'
select CAST(@tmp as datetime)
It really looks like the only way to avoid error is to check again in the function, regardless of what data type/length parameter is used. The function parameter type and length serve as a minimum check for error, not a complete one. At least that's what it looks like to me.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2008 at 3:25 pm
James Goodwin (5/20/2008)
I think everyone is missing the point of what is happening.What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
That being said, it would be nice if you could define the truncation setting for a parameter or procedure in the definition.
--
JimFive
I completely agree. Well stated.
May 21, 2008 at 5:00 am
webrunner (5/20/2008)
James Goodwin (5/20/2008)
webrunner,
Change that to varchar(8) and it will work.
'20080520AB' cannot be converted to a date, but '20080520' can.
--
JimFive
Right, but my point was that you won't always know whether the incoming data will work, for example:
Declare @Tmp varchar(8)
set @Tmp = 'A2008052'
select CAST(@tmp as datetime)
It really looks like the only way to avoid error is to check again in the function, regardless of what data type/length parameter is used. The function parameter type and length serve as a minimum check for error, not a complete one. At least that's what it looks like to me.
Thanks,
webrunner
Of course, the difference is that a conversion of a character string to a shorter VARCHAR will always work; it just gets truncated as indicated in CAST and CONVERT (Transact-SQL). However, the rules to convert a character string to a DATETIME are much more complex and restrictive, although they allow a lot of things that don't (at first) look like valid dates.-- This runs without error on SS2K5 and produces the date 9th Jan 2012 (2012-01-09 00:00:00)
declare @Tmp varchar(8)
set @Tmp='20120109'
select cast(@tmp as datetime)
set @Tmp='2012-1-9'
select cast(@tmp as datetime)
set @Tmp='2012.1.9'
select cast(@tmp as datetime)
set @Tmp='2012/1/9'
select cast(@tmp as datetime)
set @Tmp='2012-1.9'
select cast(@tmp as datetime)
set @Tmp='2012.1/9'
select cast(@tmp as datetime)
set @Tmp='1/9-12'
select cast(@tmp as datetime)
set @Tmp='1.9/12'
select cast(@tmp as datetime)
set @Tmp='1-9.2012'
select cast(@tmp as datetime)
Derek
May 21, 2008 at 7:53 am
If you force a square peg into a round hole, you will shave the corners...
I have to admit, I didn't read all the posts
...but, some seem to be implying that SQL should warn us about truncations.
Could you imagine how difficult pre-qualifying EVERYTHING would be...
If we try to divide 1/3 and put it into decimal(17,16)
SQL would warn us, "ERROR: Result will be truncated"
:sick:
May 21, 2008 at 8:32 am
thisisfutile (5/21/2008)
If you force a square peg into a round hole, you will shave the corners...I have to admit, I didn't read all the posts
...but, some seem to be implying that SQL should warn us about truncations.
Could you imagine how difficult pre-qualifying EVERYTHING would be...
If we try to divide 1/3 and put it into decimal(17,16)
SQL would warn us, "ERROR: Result will be truncated"
:sick:
Here's what happened in my case:
I got the question wrong because I thought there would be an error. I mistakenly thought that the function parameter worked similar to a column data type setting on a table.
Then I read James Goodwin's post (quoted below) that explained the details very nicely.
However, of course, just because there is no error on the parameter passed in, it doesn't mean that the data is good or that an error won't be thrown in the function itself. It just depends on the programmer's handling of the data correctly and on writing the code to anticipate and handle bad data.
Your example with 1/3 is another example of this idea - there is no error provided because SQL Server assumes that you know what you are doing and know the conversions.
Thanks,
webrunner
I think everyone is missing the point of what is happening.
What is occuring is an automatic type conversion. If I do this:
Declare @Tmp datetime
set @Tmp = '20080101'
select @Tmp
I get: 2008-01-01 00:00:00.000
I don't get a type conversion error, and I don't want one.
The same thing happens to function/procedure arguments. If the input can be converted to the parameter type then it gets converted. Truncation is considered a valid conversion in this case because the programmer is supposed to know what he is doing. In most circumstances it is probably better to have the procedure call succeed than throw an error.
That being said, it would be nice if you could define the truncation setting for a parameter or procedure in the definition.
--
JimFive
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 21, 2008 at 8:45 am
Steve Jones - Editor (5/20/2008)
If you think this should be changed, file this on Connect (connect.microsoft.com) and get people to vote for it.
And tell us, so we can vote against you.
Viewing 15 posts - 46 through 60 (of 79 total)
You must be logged in to reply to this topic. Login to reply