Inserting '' string in a datetime column -- output

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

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting '' string in a datetime column -- output

Author

Topic

andy8979
Starting Member

36 Posts

Posted - 2005-08-06 : 05:15:36

Hi,
If you insert a '' blank string in a column which is of datetime datatype then the result is '1900-01-01 00:00:00.000'
My guess is it tries to implicitly convert the string to a number and hence it inserts the value of '1900-01-01 00:00:00.000'
Please guide me if I am right.

Is it possible to insert a string in column of datatype datetime.

Declare @T Table(d datetime)
Insert Into @T Values (GetDate())
Insert Into @T Values (null)
Insert Into @T Values ('') -- What will be the output
Insert Into @T Values (0)

Select * from @T

Thanks in advance
ANUJ

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-06 : 05:19:15

>>Is it possible to insert a string in column of datatype datetime.

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-06 : 06:43:51

YES it tries to implicitly convert the string to a date as per system default date time format

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-06 : 06:48:30

>>implicitly convert the string to a date as per system default date time format

That should be implicitly convert the valid string

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-06 : 14:42:27

Since you have the code there in your post, why not just run it to see what it does?

quote:
Originally posted by andy8979

Hi,
If you insert a '' blank string in a column which is of datetime datatype then the result is '1900-01-01 00:00:00.000'
My guess is it tries to implicitly convert the string to a number and hence it inserts the value of '1900-01-01 00:00:00.000'
Please guide me if I am right.

Is it possible to insert a string in column of datatype datetime.

Declare @T Table(d datetime)
Insert Into @T Values (GetDate())
Insert Into @T Values (null)
Insert Into @T Values ('') -- What will be the output
Insert Into @T Values (0)

Select * from @T

Thanks in advance
ANUJ

CODO ERGO SUM

andy8979
Starting Member

36 Posts

Posted - 2005-08-08 : 04:08:55

Thanks for your replies,
I know what it returns but just to confirm the output I had posted this message.

and yes its true inserting a blank string value in a datatime column is stupidity, but I was checking the work done by a developer.

Anuj.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources