SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 empty date in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

82 Posts

Posted - 03/18/2013 :  10:18:47  Show Profile  Reply with Quote
what is 1753-01-01 and 1953-01-01 dates in sql? is that for empty date?

thanks

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/18/2013 :  10:29:45  Show Profile  Reply with Quote
for datetime type column, the minimum allowed date is 1753-01-01
Execute separately...
1)
DECLARE @date DATETIME = '1753-01-01'
SELECT @date

2)
DECLARE @date DATETIME = '1752-12-31'
SELECT @date
3)
DECLARE @date DATETIME = '1752-31-12'
SELECT @date


--
Chandu
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 03/18/2013 :  10:32:56  Show Profile  Reply with Quote
Just something to keep in mind: I have noticed sometimes that when you leave a date column empty sql server converts into 1900-01-01.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

kt
Yak Posting Veteran

82 Posts

Posted - 03/18/2013 :  10:41:38  Show Profile  Reply with Quote
so there are there diffrent days that SQL converts into when date column empty ?
1753-01-01,
1953-01-01
and 1900-01-01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/18/2013 :  12:53:20  Show Profile  Reply with Quote
depends on logic you use to convert those nulls. if you convert to 0 then it corresponds to datevalue of 1900-01-01

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

kt
Yak Posting Veteran

82 Posts

Posted - 03/18/2013 :  17:36:42  Show Profile  Reply with Quote
i am inherited the database from previous developers. I think they have it set “ ” and some 0. I have at least two different date in the table
1753-01-01 and 1953-01-01, don't know if any more out there. My question is how can i query only the records for those date ?
select....
from...
where year(dateAdded in ('1753', '1953') or what if i don't know if it has any other date there that sql automatice coverted into?
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 03/18/2013 :  19:27:25  Show Profile  Reply with Quote
Bonus question: Why is '1753-01-01' the starting point for the datetime data type? It seems a rather odd point of origin.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/19/2013 :  02:02:52  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

Bonus question: Why is '1753-01-01' the starting point for the datetime data type? It seems a rather odd point of origin.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber


see similar discussion here

http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000