Post reply

CONVERT function

  • January 13, 2011 at 7:24 am

    #1273024

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

  • January 13, 2011 at 7:45 am

    #1273045

    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

  • January 13, 2011 at 9:15 am

    #1273120

    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]

  • January 14, 2011 at 1:32 pm

    #1273775

    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

  • January 14, 2011 at 2:35 pm

    #1273810

    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

  • January 17, 2011 at 8:39 am

    #1274268

    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!

  • January 18, 2011 at 5:57 am

    #1274634

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

  • January 21, 2011 at 3:38 pm

    #1276839

    Thanks for the question!

  • January 22, 2011 at 12:09 am

    #1276873

    Nice question. Thanks

    Thanks

  • January 27, 2011 at 4:10 pm

    #1279454

    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

  • January 27, 2011 at 11:40 pm

    #1279517

    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

  • January 27, 2011 at 11:47 pm

    #1279519

    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

  • January 28, 2011 at 12:02 am

    #1279524

    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

  • March 3, 2011 at 12:07 am

    #1293862

    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.

  • March 3, 2011 at 9:44 am

    #1294107

    kiran.konankal (3/3/2011)

    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.

    Changing the compatibility level only changes how some T-SQL is parsed and handled, it does not make the current version of SQL Server act exactly like the version you set the compatibility level to.

    So the only way to get the correct answer is to actually run the query on SQL Server 2005.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy