Doubt with sql date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Doubt with sql date

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-10 : 06:06:22

hi all,

declare @datcol datetime
set @datcol='01/01/1900'
will @datecol will be treated as '' in its following use..
Wats the secret behind tat

ok thanx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:10:13

nope. it wont be. As it has an already assigned value.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-10 : 06:12:27

ok
then is there any importance for '01/01/1900' in sqlserver ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:12:37

see the difference

declare @datcol datetime,@datcol1 datetime
set @datcol='01/01/1900' 
select @datcol,@datcol1


output
-------------------------
datcol	datcol1
1900-01-01 00:00:00.000	NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:13:56

yup. its the starting date in sql server and corresponds to integer 0

select CAST (0 AS datetime)

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-10 : 06:20:24

hi visakh,
i have a ta ble called errorlist.
declare @ddate datetime
set @ddate='01/01/1900'
select * from ErrorList where @ddate=''

this will return all records...

why is this so...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:25:57

thats because when it casts '' to datetime it gets 1 Jan 1900 as default date value. see below

select cast('' as datetime)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:27:47

and this is what books online says which supports this behaviour

Character data representing only date or only time components will be cast to the datetime data type. The unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-10 : 06:29:34

ok thanx visakh

u cleared my doubts...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 06:30:54

Cheers