A Big SmallDateTime

Question

A Big SmallDateTime

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
March 8, 2024 at 12:00 am

#4383188

Comments posted to this topic are about the item A Big SmallDateTime

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mark Dalley SSCrazy Points: 2974 More actions · Answer 1

Seriously, why would anyone want to use this type of date? It doen't comply with any standard, can't handle seconds, and will run out of thread in the lifetimes of many of us.

I really wonder: What potential use case prompted its invention?

MarkD

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

Mark Dalley wrote:

Seriously, why would anyone want to use this type of date? It doen't comply with any standard, can't handle seconds, and will run out of thread in the lifetimes of many of us.
I really wonder: What potential use case prompted its invention?
MarkD

Uh, it holds seconds. Did you read the question???

DECLARE @Smalldatetime Smalldatetime = getdate()

SELECT @Smalldatetime

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

Mark Dalley wrote:

Seriously, why would anyone want to use this type of date? It doen't comply with any standard, can't handle seconds, and will run out of thread in the lifetimes of many of us.
I really wonder: What potential use case prompted its invention?
MarkD

Uh, it holds seconds. Did you read the question???

DECLARE @Smalldatetime Smalldatetime = getdate()

SELECT @Smalldatetime

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 4

Michael L John wrote:

Mark Dalley wrote:
Seriously, why would anyone want to use this type of date? It doen't comply with any standard, can't handle seconds, and will run out of thread in the lifetimes of many of us.
I really wonder: What potential use case prompted its invention?
MarkD
Uh, it holds seconds. Did you read the question???
DECLARE @Smalldatetime Smalldatetime = getdate()
SELECT @Smalldatetime

I've gotta agree with Mark Dalley on this one. Displaying a "place" for seconds is not the same as "handling" seconds. SMALLDATETIME rounds seconds to the nearest minute and that can round to the next day... which means it doesn't handle things correctly.

 SELECT  v1.OriginalDTString
        ,Converted_SMALLDATETIME = CONVERT(SMALLDATETIME,v1.OriginalDTString)
        ,v1.Converted_Action
   FROM (VALUES  
             ('20240310 23:59:29','RoundedDownToPrevMinute')
            ,('20240310 23:59:30','RoundedUpToNextMinute')
        )v1(OriginalDTString,Converted_Action)
;

Result:

--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.

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

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 5

Mark Dalley wrote:

Seriously, why would anyone want to use this type of date? It doen't comply with any standard, can't handle seconds, and will run out of thread in the lifetimes of many of us.
I really wonder: What potential use case prompted its invention?
MarkD

To answer that great question (and you already know this but answering the question for others)... it's only 4 bytes (2 for the date and 2 for the time) instead of 8, which was much more important back when it was invented.

Too bad they didn't do it right from the git. Seconds should have just been truncated to 0, IMHO. That way, you'd never round up to the beginning of the next day, which could also be the beginning of the next week, month, quarter, year, decade, or century.

By the same token, why on Earth was the time portion of DATETIME based on 1/300 of a second? I suspect it had something to do with machine speeds back then.

--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.

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