Post reply

DATE Conversion and Format

  • October 29, 2013 at 11:14 pm

    #284371

    Comments posted to this topic are about the item DATE Conversion and Format

  • October 30, 2013 at 1:15 am

    #1662622

    Easy one for me. 🙂 Specially after going through all the options carefully and matching each one of them with the entries in the table. 🙂

  • October 30, 2013 at 1:45 am

    #1662624

    Nice QotD thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • October 30, 2013 at 2:08 am

    #1662628

    Nice question, thanks.

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

  • October 30, 2013 at 2:40 am

    #1662635

    practical questions, a good one 🙂

  • October 30, 2013 at 2:45 am

    #1662637

    Guessed it right as that was the only option seemed right & logical to me (after eliminating all other options), then ran the query just to make sure before answering.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • October 30, 2013 at 3:24 am

    #1662642

    good question ... thanks for sharing

  • October 30, 2013 at 5:34 am

    #1662666

    All:

    I thought I would give a nice "fun" one. I believe in the basics and you would be very surprised as to how many "experienced" DBAs (Logical and\or Physical) I know got this one wrong.

  • October 30, 2013 at 5:48 am

    #1662668

    Technically both the "correct" answer and the answer marked "No rows returned, conversion error on query 3" are correct. It depends on how the Query Analyzer and query engine process the query. It could choose to multi-thread it and the first item it examined could have the error, thus no rows returned. Additionally, the table is a Temp Table with no indexes (Heap) and add in the fact that query returned is not garaunteed an order unless an explicit order by is stated. I have run into many instances where I tried to filter out by isdate(xyz) = 1 only to have the query fail because it didn't process in the order I thought it should.

  • October 30, 2013 at 6:21 am

    #1662674

    Nice QotD. The '41' was a big clue. Thanks and keep them coming.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • October 30, 2013 at 6:26 am

    #1662676

    argh...not enough sleep lately and definitely not enough coffee!

  • October 30, 2013 at 6:47 am

    #1662683

    Not a really bad query, but not really good either. It's fallen into the trap of assuming that the order in which rows in the heap are scanned is fixed, as pointed out by Venoym. It's also a bit cluttered - rather a lot of complexity to discover that 41 is not between 1 and 12 inclusive, most of that select statement is pure distraction.

    Tom

  • October 30, 2013 at 6:55 am

    #1662684

    steve.jacobs (10/30/2013)

    I believe in the basics and you would be very surprised as to how many "experienced" DBAs (Logical and\or Physical) I know got this one wrong.

    I'm not surprised. How many people use varchar columns to hold dates?

  • October 30, 2013 at 7:18 am

    #1662691

    L' Eomot Inversé (10/30/2013)

    Not a really bad query, but not really good either. It's fallen into the trap of assuming that the order in which rows in the heap are scanned is fixed, as pointed out by Venoym. It's also a bit cluttered - rather a lot of complexity to discover that 41 is not between 1 and 12 inclusive, most of that select statement is pure distraction.

    Very true. However, this is just a small example of the bad query design I have inherited. I wanted to show a small example of what I deal with everyday from our legacy systems. But still fun to try to figure out "bad" query results.

  • October 30, 2013 at 8:34 am

    #1662740

    Nice question...

    Manik
    You cannot get to the top by sitting on your bottom.

Viewing 15 posts - 1 through 15 (of 20 total)

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