March 17, 2011 at 8:30 pm
Comments posted to this topic are about the item Data conversion
March 18, 2011 at 12:17 am
I was expecting an error in table creation because of the column name "Date" since it is a SQL Server reserved word and was not embeded in a square braket.
The insert statements would not give error because SQL Server implicitely converts quoted number as numbers i.e. '1' would be considered as 1.
Thanks for the question. It was a learning for me. 🙂
March 18, 2011 at 12:40 am
I guessed correct answer but i tried to test the code.then i got error because of this
create table #test(id int identity(1,1),value int);
go
set identity_insert #test on
insert into #test
select 1,2
union all
select 2,3
select * from #test
drop table #test
the above thrown error
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table '#test' can only be specified when a column list is used and IDENTITY_INSERT is ON.
create table #test(id int identity(1,1),value int);
go
set identity_insert #test on
go
insert into #test(id,value)
select 1,2
union all
select 2,3
but the above is working fine.
even though there is same no of columns passing for both the first one thrown an error.but the second one executed without any error. I learned one point from this question.Even though we put identity insert on we have the mentioned the columns explicitly in insert statement.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
March 18, 2011 at 1:03 am
Great question.
I guess the CREATE statement will throw a bunch of people off, as it uses a reserved keyword and it has an extra comma.
Still valid though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2011 at 1:03 am
I was hoping that SQL would fail on the last comma in the create table part. But SQL was just as happy to create a table like that.
/T
March 18, 2011 at 2:13 am
Depends on the language settings. I get the following error:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Serves me right for using the 'British English' default language for my login I suppose.
Chris
March 18, 2011 at 2:16 am
Chris Howarth-536003 (3/18/2011)
Depends on the language settings. I get the following error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Serves me right for using the 'British English' default language for my login I suppose.
Chris
I was thinking the following when I read the question:
"This won't work on British systems. But I guess the question is written by an US citizen, so he'll probably forgot that there is another world out there..." 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2011 at 2:20 am
tommyh (3/18/2011)
I was hoping that SQL would fail on the last comma in the create table part. But SQL was just as happy to create a table like that./T
I agree, so, I really learned something new today.
March 18, 2011 at 2:24 am
I thought the CREATE statement will throw exception, as it uses an extra comma after field Amount.
March 18, 2011 at 2:56 am
Interesting question today.
March 18, 2011 at 3:04 am
I guessed (wrongly?) that the INSERT INTO would fail.
However, testing your code on a UK system showed that I was indeed correct and the statement fails due to implicit conversion of one of the dates resulting in an out-of-range datetime value.
Statement works fine if dates are reformatted to UK 'dd/mm/yyyy'.
The impossible can be done at once, miracles may take a little longer 🙂
March 18, 2011 at 3:27 am
Koen Verbeeck (3/18/2011)
Chris Howarth-536003 (3/18/2011)
Depends on the language settings. I get the following error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Serves me right for using the 'British English' default language for my login I suppose.
Chris
I was thinking the following when I read the question:
"This won't work on British systems. But I guess the question is written by an US citizen, so he'll probably forgot that there is another world out there..." 😀
Ha ha! I made the same mistake as the questioner... he wrote a US-specific question, forgetting there was a world out there that doesn't use the same date format... I answered it as a Brit, forgetting that the question was probably written in a different culture!
Yup, I said it would fail on the date conversion.... because it would if I were to run that code on my en-GB system! Of couse (and as this is usually the case with QOTD I should have known better) the question is actually about what would happen if you executed the code in the US.
March 18, 2011 at 3:52 am
I thought the superfluous comma would throw it out too. You learn something new every day.
I did work out that the date conversion would not work for us Brits but would be fine for middle-enders.
March 18, 2011 at 4:02 am
I was about to put that the insert would fail (because for most of us outside the USA it will, I wrongly guessed that was the point of the question).
But then noticed the final comma in the Create so went for that one, and got the wrong wrong answer 🙂
March 18, 2011 at 4:09 am
I'm half asleep this morning. Missed every one of the potential gotchas (implict conversion, US date format and extra comma), thought "this code looks ok" and selected the right answer. If i'd been more awake I'd have got the answer wrong on several counts 😀
Viewing 15 posts - 1 through 15 (of 101 total)
You must be logged in to reply to this topic. Login to reply