Don't forget this date.

Question

Don't forget this date.

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

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

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 1

SanjayAttray (4/2/2010)
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp#Why1753

Sanjay,

Thank you for the excellent reference, and its explanation of "Why 1753".

-c

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 2

Hi all,

This is an extrapolated question from the actual QoD, but I would like to know the reason (coz of knowledge gaining reason)

Why the 'mmm' part in the RESULT section giving a different value than I entered through the character string as shown below.

SET DATEFORMAT MDY

DECLARE @dmy datetime

SELECT @dmy = '12/31/99 12:13:12:012'

SELECT @dmy

RESULT:

1999-12-31 12:13:12.013

Thanks

big[M]

John

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 3

bigM (4/2/2010)
Hi all,
This is an extrapolated question from the actual QoD, but I would like to know the reason (coz of knowledge gaining reason)
Why the 'mmm' part in the RESULT section giving a different value than I entered through the character string as shown below.
SET DATEFORMAT MDY
DECLARE @dmy datetime
SELECT @dmy = '12/31/99 12:13:12:012'
SELECT @dmy
RESULT:
1999-12-31 12:13:12.013
Thanks
big[M]

The accuracy of datetime is not to the millisecond, but to 1/300 of a second. So each second is divided into 300 slices of 3 1/3 milliseconds. And because display uses milliseconds, you see the values rounded to the millisecond: 0.000, 0.003, 0.007, 0.010, 0.013, 0.017, .....

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Paul White NZ (4/2/2010)
I wonder how long it will be before someone complains that this QotD is misleading and incorrect since the question specifies "(yyyy/mm/dd)" format and the answers are all in YYYY-MM-DD format?
:laugh:

Surprised it hasn't happened yet.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Nice question.

Thanks

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Voitek (4/2/2010)
I did a cast(0 as datetime), but then I thought hmm. can negatives be converted to datetime? I picked 64000, but that failed, so then I went incrementally to -53690 beyond which the cast fails.
I don't think the QOD is misleading. The oldest date you can store is -53690, which is 1-1-1753.

Nice information. Thanks for providing it.

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

CirquedeSQLeil (4/2/2010)
Surprised it hasn't happened yet.

vk-kirov covered it!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Paul White NZ (4/2/2010)
CirquedeSQLeil (4/2/2010)
Surprised it hasn't happened yet.
vk-kirov covered it!

I found that comment funny.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I found this one fairly easy. Good discussion, which adds to question.

Anipaul SSC-Insane Points: 24681 More actions · Answer 10

Anipaul

SSC-Insane

Points: 24681

April 5, 2010 at 2:30 am

#1145605

I didn't knew it.. Great one to know...

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 11

This is a very interesting question, thank you Carla. I knew that 1753-01-01 was selected as the first available date, but had no clue why the SQL Server team chose it. The number -53690 does not appear to be of any significance, so I learned something new today.

Oleg

nitin.pathak 81605 SSC Journeyman Points: 81 More actions · Answer 12

same with me ...whenever i selects date columns it shows same..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Lynn Pettis (4/2/2010)
I found this one fairly easy. Good discussion, which adds to question.

I have to admit, I did not find this one as easy. I did have to do some checking and research.

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

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 14

Any idea what the latest date is? I got as far as 500,000 days into the future before i gave up, I doubt i'd have much use for this unless i'm using DBCC timewarp though 🙂

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 15

From MS SQL documentation:

datetime: January 1, 1753, through December 31, 9999

smalldatetime: January 1, 1900, through June 6, 2079

Note:

datetime is stored as two 4-byte integers for days from 1/1/1900 and milliseconds since midnight.

smalldatetime is stored as two 2-byte integers for days from 1/1/1900 and minutes since midnight.