Don't forget this date.

Question

Post reply

Don't forget this date.

SqlOnMyMind

SSCertifiable

Points: 5062
More actions
April 1, 2010 at 10:05 pm

#222739

Comments posted to this topic are about the item Don't forget this date.

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

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 1

Hi,

When I using SQL Server it show default of SQL Server as 1900-01-01.I thought that it was the least date.

But my answer is wrong.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

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

When I insert the datatime row into a table as below, I get different result. Any idea?

use tempdb

go

create table test1 (dat datetime)

go

insert into test1 values('1753-01-01')

select * from test1

RESULT:

1753-01-01 00:00:00.000

insert into test1 values(1753-01-01)

select * from test1

RESULT:

1904-10-18 00:00:00.000

:unsure:

Thanks

big[M]

John

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

bigM (4/2/2010)
When I insert the datatime row into a table as below, I get different result. Any idea?
use tempdb
go
create table test1 (dat datetime)
go
insert into test1 values('1753-01-01')
select * from test1
RESULT:
1753-01-01 00:00:00.000

Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted. (And with month and day both equal to 1, it would in this case not even make a difference). So the conversion results in the datetime value equivalent to January 1st, 1753.

insert into test1 values(1753-01-01)
select * from test1
RESULT:
1904-10-18 00:00:00.000
:unsure:

In this case, there are no quotes. SQL Server will interpret 1753-01-01 as an expression that consists of three integers and two subtraction symbols. It will compute the result, which is the integer value 1751. This then has to be stored in a datetime column, so implicit integer to datetime conversion kicks in. And that conversion rule is that the resulting datetime value is computed by adding the integer number of days to the base date of Jan 1st, 1900. The result, October 18th, 1904, is apparently exactly 1751 days after this base date.

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/

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

Hugo Kornelis (4/2/2010)
Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted.

I believe the ODBC formats are guaranteed too, since they have a fixed format definition, do not depend on DATEFORMAT, and are multi-language.

On the downside, the ODBC formats are not an international standard, only return DATETIME (not any of the new 2008 types), and are non-deterministic. Not ideal, then 🙂

Examples:

SELECT { ts '2010-04-02 23:49:30.723' };

SELECT { d '2010-04-02' };

SELECT { t '23:49:30.723' };

Books Online:

Using Date and Time Data

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp#Why1753

SQL DBA.

Voitek Ten Centuries Points: 1366 More actions · Answer 7

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.

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

Voitek (4/2/2010)
I don't think the QOD is misleading.

Neither does anyone else...so far. Give it time 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 9

Why the question specifies the slash datetime format (yyyy/mm/dd) while all the answers are in the dash datetime format (yyyy-mm-dd)? This is completely misleading and incorrect! 😛

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

*groan*

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/

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

vk-kirov (4/2/2010)
Why the question specifies the slash datetime format (yyyy/mm/dd) while all the answers are in the dash datetime format (yyyy-mm-dd)? This is completely misleading and incorrect! 😛

Awesome! Very funny :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 12

vk-kirov

SSCertifiable

Points: 7686

April 2, 2010 at 6:24 am

#1144691

By the way, which event took place on 1776-01-01?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 13

I knew the answer, but I didn't know the bit about the Gregorian calendar....interesting.

sjimmo SSChampion Points: 11139 More actions · Answer 14

My only complaint is that it is true.

Had to do a conversion back in 6.5 days, and maintain dates going back to 1600's. Had to be a bit creative on that;-)

Good question

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan