TRY_CONVERT smaller

Question

Post reply

TRY_CONVERT smaller

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

g.maxfield Default port Points: 1471 More actions · Answer 1

g.maxfield

Default port

Points: 1471

February 18, 2016 at 4:44 am

#1859507

Which version does this code work on?

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

BWFC (2/18/2016)
To be pedantic, it depends on what edition you run the code. I know that the unwritten rule is assume the latest version but many people testing that, like I did, will get the wrong answer.

Oh, this.

Tried first on SQL2008R2, got an error. But not the error I was expecting. So tried on a SQL2014, and got the answer that was expected.

And now I've got an extra point. ;-):-P

Thomas Rushton
blog: https://thelonedba.wordpress.com

g.maxfield Default port Points: 1471 More actions · Answer 3

g.maxfield

Default port

Points: 1471

February 18, 2016 at 6:54 am

#1859552

I tried on SQL 2014 but still got the 🙁

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

g.maxfield (2/18/2016)
I tried on SQL 2014 but still got the 🙁

Are you sure the instance you're connected to is 2012+? I know you said you're using SSMS 2014, but you could still be connected to a pre-2012 instance.

In the same query window where you get the error for the QotD query, what does SELECT @@VERSION return?

Also, since it might be a 2012+ instance, but with the query run in a database with a pre-2012 compatibility level, what does this return?

SELECT compatibility_level

FROM sys.databases

WHERE database_id=DB_ID();

Cheers!

Bobby Russell SSCrazy Points: 2801 More actions · Answer 5

I took the error answer since it could have been either that one or '2' depending on which version of SQL server you are running on. When there are two right answers, what else can a guy do? 🙂

g.maxfield Default port Points: 1471 More actions · Answer 6

SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members

Last Login: Today @ 2:04:58 PM

Points: 1,712, Visits: 4,801

Thanks

It appears I have SQL Server 12.0.2000 (top of the instance). SMS Showing SQL Server 2014 on the opening screen. Could be the older version - I'll need to upgrade. Thanks g:cool:

g.maxfield Default port Points: 1471 More actions · Answer 7

SS Committed

SELECT compatibility_level

FROM sys.databases

WHERE database_id=DB_ID();

Your code resulted in compatibility_level 100! :unsure:

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

g.maxfield (2/18/2016)
SS Committed
SELECT compatibility_level
FROM sys.databases
WHERE database_id=DB_ID();
Your code resulted in compatibility_level 100! :unsure:

Then that's the issue. Even on a 2012 or higher instance, the compatibility level of the database in which the code was being run would also have to be for 2012 or higher (110+) for the code to work.

Cheers!

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 9

Hugo Kornelis (2/18/2016)
Carlo Romagnano (2/18/2016)
That's easy!
But, try to answer this:
"What's the resut?"
SELECT TRY_CONVERT(VARCHAR(1), {D'2011-MAY-01'})
:-D:-D:-D
The correct answer to this question is "it depends".
Now my question to you is if you can figure out what it depends on.
(And no, I am not referring to the version of SQL Server)

Would it be based on regional configuration of the date format? (providing you supplied the date in the accepted format as pointed out earlier i the thread)

g.maxfield Default port Points: 1471 More actions · Answer 10

Thanks SSC. Now I've learnt something new! Cheers:-D

kumar1pr SSC Enthusiast Points: 141 More actions · Answer 11

Anyone can point to the reason why first is converted while second one fail?

SELECT TRY_CONVERT(DATE,'2016/10/05')

:w00t:

SELECT TRY_CONVERT(DATE,'12/31/2010')

:hehe:

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 12

BWFC (2/18/2016)
To be pedantic, it depends on what edition you run the code. I know that the unwritten rule is assume the latest version but many people testing that, like I did, will get the wrong answer.
Still, I've got my point back by complaining about it 😎

No points back. It doesn't depend on edition, or it shouldn't. It does depend on version. This is a 2012+ T-SQL construct.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 13

kumar1pr (2/18/2016)
Anyone can point to the reason why first is converted while second one fail?
SELECT TRY_CONVERT(DATE,'2016/10/05')
:w00t:
SELECT TRY_CONVERT(DATE,'12/31/2010')
:hehe:

Because the first has a format that isn't misleading, while the second does. 😎

That's why I prefer to work with ISO 8601 formats

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

abjames Old Hand Points: 339 More actions · Answer 14

I got an error.

Msg 195, Level 15, State 10, Line 1

'VARCHAR' is not a recognized built-in function name.

Marcia J SSCertifiable Points: 5649 More actions · Answer 15

Hugo Kornelis (2/18/2016)
I *almost* gave the answer that would have been correct for SELECT TRY_CONVERT(VARCHAR(1), 234523). Almost.
Also, I would like to know how many people would have selected NULL if that had been an option.

I have to admit I would have been one of those people.