CONVERT function

Question

Post reply

CONVERT function

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

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

OCTom SSChampion Points: 11785 More actions · Answer 1

OCTom

SSChampion

Points: 11785

January 13, 2011 at 6:43 am

#1272990

Thanks for a tough but fair question.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

This definitely required some reading. I will definitely be able to use what I learned. Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Toreador (1/13/2011)
You mean I was supposed to read all the words in your reply?!

For the one time Hugo has fairly short reply 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Koen (da-zero) (1/13/2011)
Great, but difficult question.
I had to read the section on converting binary string data on the MSDN page for CAST and CONVERT a few times to get it.
But only 1 point? It deserves a lot more...

I agree, just for making sure I checked the exactly right boxes I think it's worth 2... I spent a good 5 minutes reading and re-reading the options

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mccork SSC-Insane Points: 22288 More actions · Answer 5

How long has SQL 2008 been around? Close to 3 years.

And how long have I known about CONVERT styles for these non-date datatypes? Close to 3 minutes.

Thanks for the question. Got me reading BOL and discovering new - and useful - functionality (which I probably should have known about well before now.... but there you go).

Cheers,
- Mark

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jts2013 Hall of Fame Points: 3226 More actions · Answer 7

I think it would have been clearer if the question mentioned Binary in the title or Differences between SQL 2005 and 2008. Looking at the code I had no idea what it meant so had to guess - and that was after running it in SQL 2008!

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 8

I don't have access to SQL 2005 and i tried to run against 2008 and selected answers accordingly

UMG Developer SSChampion Points: 13482 More actions · Answer 9

UMG Developer

SSChampion

Points: 13482

January 21, 2011 at 3:38 pm

#1276839

Thanks for the question!

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Hardy21

SSCrazy Eights

Points: 9708

January 22, 2011 at 12:09 am

#1276873

Nice question. Thanks

Thanks

dennisp SSC Eights! Points: 811 More actions · Answer 11

I know a lot about 2k8, but wasn't familiar with this at all.

Plus, the question reminds me of an episode of Whose line is it anyway where they do a spoof of Do you want to be a Millionaire?

Itzik:

I'd like to see MS provide parameter helpers to some of the functions, simliar to .NET

For example:

select convert(varchar(10), getdate(), styleMMDDYY)

select convert(varchar(10), getdate(), styleDDMMYY)

or

select convert(varchar(10), getdate(), 'mmddyy')

convert(varchar(10), getdate(), 'ddmmyyyy')

number formatting:

select convert(varchar(20), 1234, '###zzz')

-dennis

Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

dennisparks (1/27/2011)
I know a lot about 2k8, but wasn't familiar with this at all.
Plus, the question reminds me of an episode of Whose line is it anyway where they do a spoof of Do you want to be a Millionaire?
Itzik:
I'd like to see MS provide parameter helpers to some of the functions, simliar to .NET
For example:
select convert(varchar(10), getdate(), styleMMDDYY)
select convert(varchar(10), getdate(), styleDDMMYY)
or
select convert(varchar(10), getdate(), 'mmddyy')
convert(varchar(10), getdate(), 'ddmmyyyy')
number formatting:
select convert(varchar(20), 1234, '###zzz')
-dennis

That is a nice suggestion, but what is the point? The database purpose is to store data efficiently. Not to present the data nicely.

That is the purpose of the end-user application, such as SSRS. And in SSRS that functionality is present.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

dennisp SSC Eights! Points: 811 More actions · Answer 13

But if you suggest the database is not to format data nicely, why even have the convert, cast, substring, etc... functions??

I would argue that a significant amount of database development time is invested. Why not make that development time more efficient, just like in .net

I have intellisense in .net, and now even SQL, so why not provide intellisense on the SQL function parameters or more mneumonic intuitive values.

Resharper, c# does.

Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 14

dennisparks (1/27/2011)
But if you suggest the database is not to format data nicely, why even have the convert, cast, substring, etc... functions??

I think CAST and CONVERT are more used to convert the data types, not to re-format the data in the same data type.

SUBSTRING and other functions can also be used to extract information, not just to layout things.

dennisparks (1/27/2011)
I would argue that a significant amount of database development time is invested. Why not make that development time more efficient, just like in .net
I have intellisense in .net, and now even SQL, so why not provide intellisense on the SQL function parameters or more mneumonic intuitive values.
Resharper, c# does.

Indeed, a lot of development time is spent on such tasks.

But again, in my opinion, layouting the data nicely is more of a task for the end-user tool (unless it is really lightweight).

On the other hand, if you are using SQL to present your data to end users, then such functions could be useful. But I haven't really felt the need for it (yet).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kiran.konankal SSC Veteran Points: 236 More actions · Answer 15

bitbucket-25253 (1/12/2011)
Comments posted to this topic are about the item <A HREF="/questions/T-SQL/71904/">CONVERT function</A>

I have only sql 2008 installed in my desk. i tried executing the select's by changing the compatibility mode to 90 and 100 manually.

And i got :

--2005:

EXEC dbo.sp_dbcmptlevel @dbname=N'MyDB', @new_cmptlevel=90

go

select convert(varchar(12),0x49747A696B,0) AS [Column 0],

convert(varchar(12),0x49747A696B,1) AS [Column 1],

convert(varchar(12),0x49747A696B,2) AS [Column 2]

Column 0 Column 1 Column 2

------------ ------------ ------------

Itzik 0x49747A696B 49747A696B

--2008:

EXEC dbo.sp_dbcmptlevel @dbname=N'MyDB', @new_cmptlevel=100

go

select convert(varchar(12),0x49747A696B,0) AS [Column 0],

convert(varchar(12),0x49747A696B,1) AS [Column 1],

convert(varchar(12),0x49747A696B,2) AS [Column 2]

Column 0 Column 1 Column 2

------------ ------------ ------------

Itzik 0x49747A696B 49747A696B

Please light me if am wrong.if wrong, then what is rite? could you pls elobrate a bit.