DateTime Puzzle

  • Nice easy question to begin the week.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Nils Gustav Stråbø (5/7/2012)


    SQL Kiwi (5/5/2012)In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

    Which would lead me to believe that each "tick" in the time integer is actually 3 1/3 ms and not 3.3 ms, which makes sense regarding the rounding.

    Thanks! Of course that's right - not sure how I managed to confuse myself before. Must be age catching up with me 🙂

  • To those who said the answer was easy - is this because you know how SQL handles implicit conversions of this sort, or because you ran the code? I didn't think it was easy at all, because I try not to run the code before answering, and because I would never dream of trying to cast non-date values in this way!

  • Toreador (5/8/2012)


    To those who said the answer was easy - is this because you know how SQL handles implicit conversions of this sort, or because you ran the code? I didn't think it was easy at all, because I try not to run the code before answering, and because I would never dream of trying to cast non-date values in this way!

    It's one of those cases where it's really easy - if you know the answer already ;-). I can't see that you would be able to work this out from first priciples without doing some reading. I'd say all of the people who've said it's easy know about these implicit conversions.

    As you (and quite a number of the earlier respondents) say, it's not something that you should really be using without a very good reason.

  • What I find more disturbing than this reliance on implicit conversion and thinking of a DATETIME value being held as a float is the reference to 12.00 AM. Surely everyone knows midnight and noon are neither PM nor AM.

  • Nice question, even better discussion. Thanks all!

  • marlon.seton (5/8/2012)


    What I find more disturbing than this reliance on implicit conversion and thinking of a DATETIME value being held as a float is the reference to 12.00 AM. Surely everyone knows midnight and noon are neither PM nor AM.

    This article sums the situation up quite well: http://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight

    Even if we take the position that AM and PM, taken literally from their meanings in Latin, do not apply to midnight and noon, there are still certain practicalities to consider. For example, certain CONVERT styles specify AM and PM. By convention, we set midnight as AM and noon as PM:

    SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 00:00:00', 120), 109);

    SELECT CONVERT(char(26), CONVERT(datetime, '2012-07-11 12:00:00', 120), 109);

  • The sooner we stop using datetime the better. We now have Date & Time Data Types and DateTime2.

    It's fairly straight forward: Dates and Times should be stored as such. If a Timestamp is required then use Datetime2 with its correct level of accuracy.

  • Easy and straight forward one. Thanks

  • Good question. Thanks for submitting.

    http://brittcluff.blogspot.com/

Viewing 10 posts - 16 through 25 (of 25 total)

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