Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Don't forget this date. Expand / Collapse
Author
Message
Posted Thursday, April 1, 2010 10:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 1,556, Visits: 1,849
Comments posted to this topic are about the item Don't forget this date.
Post #895450
Posted Friday, April 2, 2010 12:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #895465
Posted Friday, April 2, 2010 2:03 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 10:53 PM
Points: 290, Visits: 714
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



Thanks
big[M]


John
Post #895483
Posted Friday, April 2, 2010 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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



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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #895489
Posted Friday, April 2, 2010 4:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895515
Posted Friday, April 2, 2010 4:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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?





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895517
Posted Friday, April 2, 2010 5:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
The ultimate guide to the datetime datatypes

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


SQL DBA.
Post #895537
Posted Friday, April 2, 2010 6:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 24, 2014 3:31 PM
Points: 1,230, Visits: 158
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.
Post #895542
Posted Friday, April 2, 2010 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
Voitek (4/2/2010)
I don't think the QOD is misleading.

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895548
Posted Friday, April 2, 2010 6:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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!
Post #895551
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse