Datetime and NULL Parameter? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Datetime and NULL Parameter?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-19 : 02:13:21

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-19 : 02:18:20

quote:
Originally posted by kwikwisi

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

Nope. All datatype can accept NULL as a value


declare @dt datetime
select @dt = NULL
select @dt

"if that field is optional,how can i solve?"


select *
from   sometable
where  @dt is null
or     (
           @dt is not null and date_col = @dt
       )

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 02:28:30

quote:
Originally posted by kwikwisi

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

depends on whether its declared as nullable or not.
if that field is optional define it as nullable so that it will hold NULL value in cases where you dont wish to store a value

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-19 : 02:49:17

If user sets blank(null) for datetime data type, got error.
If user sets null for other data type ,no error found.

quote:
Originally posted by khtan

quote:
Originally posted by kwikwisi

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

Nope. All datatype can accept NULL as a value

declare @dt datetime
select @dt = NULL
select @dt

"if that field is optional,how can i solve?"

select *
from sometable
where @dt is null
or (
@dt is not null and date_col = @dt
)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 02:55:43

quote:
Originally posted by kwikwisi

If user sets blank(null) for datetime data type, got error.
If user sets null for other data type ,no error found.

quote:
Originally posted by khtan

quote:
Originally posted by kwikwisi

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

Nope. All datatype can accept NULL as a value

declare @dt datetime
select @dt = NULL
select @dt

"if that field is optional,how can i solve?"

select *
from sometable
where @dt is null
or (
@dt is not null and date_col = @dt
)

KH
[spoiler]Time is always against us[/spoiler]

ok. so user will pass (null) as value?

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-19 : 02:57:26

correct.
it's optional for user.

quote:
Originally posted by visakh16

quote:
Originally posted by kwikwisi

If user sets blank(null) for datetime data type, got error.
If user sets null for other data type ,no error found.

quote:
Originally posted by khtan

quote:
Originally posted by kwikwisi

Datetime data type can't accept Null value,right?
if that field is optional,how can i solve?

With Regards,
kwi

Nope. All datatype can accept NULL as a value

declare @dt datetime
select @dt = NULL
select @dt

"if that field is optional,how can i solve?"

select *
from sometable
where @dt is null
or (
@dt is not null and date_col = @dt
)

KH
[spoiler]Time is always against us[/spoiler]

ok. so user will pass (null) as value?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 02:59:28

the use

Datefield=@DateParam OR NULLIF(@DateParam,'(null)') IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-19 : 04:02:09

@DateParam IN (Datefield, '(null)')

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 05:41:28

getting incorrect syntax error..
@dc datetime
as
insert into test(DateField)
values(@dc IN (DateField, '(null)'))

quote:
Originally posted by Peso

@DateParam IN (Datefield, '(null)')

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-20 : 06:48:01

You can to insert value of @dc into test table ?


insert into test(DateField)
select nullif (@dc, '(null)')

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-20 : 07:21:19

can't accept null,Plz let me know datetime data type can accept null value or not?(already set as nullable)

quote:
Originally posted by khtan

You can to insert value of @dc into test table ?

insert into test(DateField)
select nullif (@dc, '(null)')

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-20 : 08:13:25

This is going to go on for weeks ....

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 08:54:11

I guess he sould have read this blog post http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx ?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-21 : 03:36:11

quote:
Originally posted by kwikwisi

can't accept null,Plz let me know datetime data type can accept null value or not?(already set as nullable)

quote:
Originally posted by khtan

You can to insert value of @dc into test table ?

insert into test(DateField)
select nullif (@dc, '(null)')

KH
[spoiler]Time is always against us[/spoiler]

How are you sending a NULL value?

See the difference

Declare @date datetime
set @date=NULL -- This NULL is not a string
select @date
Go
Declare @date datetime
set @date='NULL' -- This 'NULL' is a string which is not valid to be datetime
select @date

Madhivanan

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-22 : 22:58:43

I add like :

insert into testing(DC)
values( nullif (@dc, '(null)'))

But getting error like:"String was not recognized as a valid DateTime. "
Really thanks for reply.

quote:
Originally posted by madhivanan

quote:
Originally posted by kwikwisi

can't accept null,Plz let me know datetime data type can accept null value or not?(already set as nullable)

quote:
Originally posted by khtan

You can to insert value of @dc into test table ?

insert into test(DateField)
select nullif (@dc, '(null)')

KH
[spoiler]Time is always against us[/spoiler]

How are you sending a NULL value?

See the difference

Declare @date datetime
set @date=NULL -- This NULL is not a string
select @date
Go
Declare @date datetime
set @date='NULL' -- This 'NULL' is a string which is not valid to be datetime
select @date

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-23 : 03:12:06

quote:
Originally posted by kwikwisi

I add like :

insert into testing(DC)
values( nullif (@dc, '(null)'))

But getting error like:"String was not recognized as a valid DateTime. "
Really thanks for reply.

quote:
Originally posted by madhivanan

quote:
Originally posted by kwikwisi

can't accept null,Plz let me know datetime data type can accept null value or not?(already set as nullable)

quote:
Originally posted by khtan

You can to insert value of @dc into test table ?

insert into test(DateField)
select nullif (@dc, '(null)')

KH
[spoiler]Time is always against us[/spoiler]

How are you sending a NULL value?

See the difference

Declare @date datetime
set @date=NULL -- This NULL is not a string
select @date
Go
Declare @date datetime
set @date='NULL' -- This 'NULL' is a string which is not valid to be datetime
select @date

Madhivanan

Failing to plan is Planning to fail

Didnt you really understand my previous post?

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 10:25:32

Can u tell me table structure
and how u are passing the data ..?
clearly.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-23 : 10:33:41

quote:
Originally posted by kwikwisi

I add like :

insert into testing(DC)
values( nullif (@dc, '(null)'))

You don't need NULLIF. If @DC is a DateTime, you just INSERT it:

insert into testing(DC)
values( @dc)

That's it!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources