Are the posted questions getting worse?

  • Ok!  Keeping in touch with the original intent of this thread, I've just found the perfect 2nd question to ask people I'm interviewing.  It's a perfect fit for my infamous "How do you get the current date and time using T-SQL question that 20 out of 22 candidates couldn't answer before I stopped counting.  It's NOT a trick question, either.

    No need to pile on... I'm just gobsmacked a bit since it's coming from a person who's been asking questions on SQL Server Central for 2 years implying that they've worked with SQLServer/T-SQL for at least that long...

    https://www.sqlservercentral.com/forums/topic/function-code

    For those that are interested that don't already know, I ask that first question because it's super easy and I ask it after I explain that I don't ask trick questions, esoteric questions, or "Manhole Cover" questions because I know they're nervous as hell and want to give them something easy.  I had no idea that it would become a "litmus strip" test.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Which of these is your question:

    "What does the 101 format produce on a date converted to varchar?"

    "How do you convert a date to varchar and format it?"

    "What is the worst way you can think of to format a date?"

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Time spent working with something does not always reflect how well you have mastered it.

    You might want to write a simple Exists / Not Exists query and add something like ‘ Sprinkle some pixie dust here’. Use as a interview question.

    I remember as a kid taking the National Math Exam. It was always a mix of easy, some hard, and some trick common sense questions. Fun to see how many stumbled through some of the questions.

  • jonathan.crawford wrote:

    Which of these is your question:

    "What does the 101 format produce on a date converted to varchar?"

    "How do you convert a date to varchar and format it?"

    "What is the worst way you can think of to format a date?"

    Heh... spot on and much appreciated but my question would be "What does this code do"?  If they can tell me, my followup question would be "Can you tell me a better way to accomplish what this function was (and I use the term loosely) "designed" to do"?  If they don't mention why scalar functions suck, I'll ask them "Why shouldn't this be done in a scalar function"?  If they can discuss that intelligently, then I'll ask "What improvements have they made for scalar functions in 2019"?

    Every question I ask has a "decision tree" as to what I'm going to ask next and how I get the candidate to get over the jitters and start talking and offering additional information and "opinion" about the things we're discussing.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • With regard to DateTimes...

    We have a core application, used for legal case management,  partly black box. The application always converts the local datetimes, entered into the front end, to UTC Datetime  for storage, then converts to local datetime when read. Sensible, it's designed to be used in multiple timezones. We only operate in one timezone, however daylight saving time also causes the conversion of course.

    Got that nasty prickly feeling that I've been doing something badly with regard to datetimes for a long time. In my "infancy", and the white heat of becoming Accidental, Lone DBA at the point this system was commissioned, while also having to produce many, many reports from it, I did something I probably should have revisited!

    When reporting, we were advised to use the supplied function UTCToLocalTime on dates:

    CREATE FUNCTION [dbo].[UTCToLocalTime](@date [DATETIME])
    RETURNS [DATETIME] WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [ApplicationNameCLR].[UserDefinedFunctions].[UTCToLocalTime]
    GO

    Obviously I've never used the function on columns in a JOIN, or WHERE clauses, only in SELECT.  The function annoyingly returns '1900-01-01 00:00:00.000' when it encounters a NULL date, so very early on I created (please be gentle):

    CREATE FUNCTION [dbo].[udUTCToLocalTime](@date DATETIME)
    RETURNS DATETIME
    AS
    BEGIN

    RETURN NULLIF(dbo.UTCToLocalTime(@date),'1900-01-01 00:00:00.000')

    END

    It's been working well, producing accurate results but I'm sure there is a nasty overhead adding milliseconds to reports. Now I'm wondering whether my time (should I ever get any) would be well spent bypassing the assembly and looking for a way to create my second function using native SQL techniques.

    (This is the system which caused me to get hauled over hot coals by JC because it uses a CodeLookup table and I wanted to do a wierd, one-off query using an rCTE to produce a hierarchy of the codes 😉 )

    https://www.sqlservercentral.com/forums/topic/unusual-recursive-cte-with-non-unique-codes (JC tongue lashing since removed, I see)

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson

  • Jeff Moden wrote:

    "How do you get the current date and time using T-SQL question that 20 out of 22 candidates couldn't answer before I stopped counting.  It's NOT a trick question, either.

    Do you mean the current date and time where the interview happens or the datetimeoffset of the SQL Server instance?  It is a trick question lol

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    Jeff Moden wrote:

    "How do you get the current date and time using T-SQL question that 20 out of 22 candidates couldn't answer before I stopped counting.  It's NOT a trick question, either.

    Do you mean the current date and time where the interview happens or the datetimeoffset of the SQL Server instance?

    Heh... exactly.  That would have been a good question from the candidate because it would demonstrate that know something about dates and times.  Of course, if they answer like that, my retort would be "Excellent question.  Tell me how you'd get both using T-SQL".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Steve Collins wrote:

    Jeff Moden wrote:

    "How do you get the current date and time using T-SQL question that 20 out of 22 candidates couldn't answer before I stopped counting.  It's NOT a trick question, either.

    Do you mean the current date and time where the interview happens or the datetimeoffset of the SQL Server instance?

    Heh... exactly.  That would have been a good question from the candidate because it would demonstrate that know something about dates and times.  Of course, if they answer like that, my retort would be "Excellent question.  Tell me how you'd get both using T-SQL".

    My "favourite" response ever for that question is "do you mean now?"

    😎

    With a bit of a pun, my reaction was: "what part of current do you not understand now"

  • Eirikur Eiriksson wrote:

    My "favourite" response ever for that question is "do you mean now?"

    We're in now now

    https://www.youtube.com/watch?v=nRGCZh5A8T4

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • david.edwards 76768 wrote:

    With regard to DateTimes...

    I had to write such a thing because we have to return calls to the customers of our customers.  They cover all U.S./Canadian and their territories time zones in the "NANP" (North American Numbering Plan).  It includes area codes and exchanges that are on the other side of the International Date Line and places that do or do not have Daylight Saving Time and a couple of places that have half hour offsets for that instead of 1 hour offsets.

    We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.

    We also have several offices and they're in several different timeszones, as well.

    I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed.   It would, of course, have been better to have the front-end software do that based on whatever machine they were using to talk to the database, especially so "local formatting" could take place on the client side but they didn't want that.  They wanted it based on the time/registry entries of the SQL Server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson wrote:

    With a bit of a pun, my reaction was: "what part of current do you not understand now"

    Now that's funny! 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    david.edwards 76768 wrote:

    With regard to DateTimes...

    8<

    We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.

    We also have several offices and they're in several different timeszones, as well.

    I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed. >8

    I'm somewhat relieved that you were not able to pull out a simple, native, SQL  solution which performs in microseconds and scales infinitely.

    I can relax and keep that filed in the "If it ain't broke, dont fix it" locker 😀

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson

  • david.edwards 76768 wrote:

    Jeff Moden wrote:

    david.edwards 76768 wrote:

    With regard to DateTimes...

    8<

    We loaded up a lookup table for the different time zones and sub-time zones and purchased some data that lists every NPA/NXX (Area Code and Exchange in telephone-speak) that also had an entry with the time zone number.

    We also have several offices and they're in several different timeszones, as well.

    I know it sounds weird but I very successfully had SQL Server read the timezone information from the registry of whatever server it was working on to do the timezone calculations and to auto-magically change when DST changed. >8

    I'm somewhat relieved that you were not able to pull out a simple, native, SQL  solution which performs in microseconds and scales infinitely.

    I can relax and keep that filed in the "If it ain't broke, dont fix it" locker 😀

    The only word missing in my solution is "native". 😀  And, I agree with the "If it ain't broke..." notion because, while change is inevitable, change for the better is not. 😀  "Timex" code (takes a lickin' and keeps on tickin') is beauf!

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Collins wrote:

    Eirikur Eiriksson wrote:

    My "favourite" response ever for that question is "do you mean now?"

    We're in now now

    https://www.youtube.com/watch?v=nRGCZh5A8T4%5B/quote%5D

    As of 2020-09-25 16:42 BST, you owe me a keyboard!

    😎

    Should have learned to have the keyboards splatter proof, unfortunately, I don't have currently done so now;)

  • Haha! Look what appeared in today's daily digest email...

    https://www.sqlservercentral.com/blogs/converting-a-datetime-to-utc

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson

Viewing 15 posts - 65,146 through 65,160 (of 65,264 total)

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