Avoid Default Datetime - 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

Avoid Default Datetime

Author

Topic

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 07:29:47

Hi All,

i have a table as below
create table temp
(
Number int,
DateError datetime
);

insert into temp values(13,' ');

i get
Number DateError
------------------
13 1900-01-01 00:00:00.000

Now, i want "1900-01-01 00:00:00.000" and just want NULL or blank and ofcourse not 1900-01-01 00:00:00.000 for my further processing.
I have a problem, i have to formath string send it as second value which is simple ' '.
How can i modify my Table definition above to achieve this.
Thanks in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 07:32:15

You can insert NULL, but not ' ' in date column.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 07:37:59

Empty string will be converted to 1900-01-01 00:00:000

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 07:38:04

Hi Harsh,
Apparently, i am populating a variable in my code and filling my insert statement.
It gets some valid date or '' when populated.
I cannot afford to have NULL (and not even 'NULL' as it throws error to be inserted) as my parameter.
I hope you can imagine the scenario.

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 07:39:57

Hi Madhivanan,
Is there way to design my table or set some option so that even if i pass empty string i dont get this weired date.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 08:06:55

Weired?
An empty string or Zero value default to origin date in SQL Server which happens to be January 1, 1900.

Which date would you like to insert when passing an empty string to the stored procedure? Today's date?

Peter Larsson
Helsingborg, Sweden

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 08:12:59

Hi Peso,
I dont want to insert any date there. i would like to have just NULL or Blank by any means.
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 08:21:42

set @MyDatetimeInputParameter = CASE WHEN @MyDatetimeInputParameter = '' THEN NULL WHEN ISDATE(@MyDatetimeInputParameter) = 1 THEN @MyDatetimeInputParameter ELSE NULL END

insert into temp values(13, @MyDatetimeInputParameter);

Peter Larsson
Helsingborg, Sweden

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 08:26:32

Hi Peso,
I am afraid i still insert the default date if i use the above piece of script( NULL in the insert statement as insert into temp values(13,NULL);
).
Thanks

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-06 : 08:29:51

Hi Peso,
I beg your pardon. The above script works well, but i have problems in implementing the same in my C++ code. But it was of great help.
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-06 : 12:21:38

I was going to suggest that you could replace the CASE statement with a NULLIF : INSERT INTO temp VALUES(13, NULLIF(@MyParam, '')). But, are you building this as dynamic sql in your c++ code or..?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources