Adding N/A to database - 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

Adding N/A to database

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-21 : 19:41:04

How would I write if BMIDte ="" then put in "N/A"" into the database instead? That is a date field so can I still do this?


@lname varchar(50),
@fName varchar (50),
@dob datetime,
@OutVisitRtb char(1),
@BMIRbn char(1),
@BMIDte datetime,
@BMIVal char(3),
@BMIPerctxt char(3),
@GenderRbn char(1),
@Commtxt varchar (1000)

AS INSERT INTO AdultBMI

(lname,
fname,
dob,
OutVisitRtb,
BMIRbn,
BMIDte,
BMIVal,
BMIPerctxt,
GenderRbn,
Commtxt)

values

(
@lname,
@fname,
@dob,
@OutVisitRtb,
@BMIRbn,
@BMIDte,
@BMIVal,
@BMIPerctxt,
@GenderRbn,
@Commtxt)



GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 19:44:53

No. You can't store text into a date field

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 19:52:41

N/A is not a valid date value so unless you make it varchar you cant store it.
Usually default value stored for date is 1900-01-01 which is the base date

see


select cast('' as DATETIME)

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-21 : 19:57:47

Thanks I need to maintain the datefield so the 1900 date will have to go into the database. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 20:04:56

quote:
Originally posted by JJ297

Thanks I need to maintain the datefield so the 1900 date will have to go into the database. Thanks!

yep...and in front end check if its basedate and convert it to "N/A" if you want

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-21 : 20:42:00

Okay thanks again I will!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 21:07:00

welcome

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-22 : 06:44:31

What is wrong with NULL? You can leave it to have NULL

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-22 : 09:02:45

NULL wasn't going into the database 1900/01/01 is instead. I can put null in there how can I do that to the query?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 10:29:17

There are several options. Two of them are to use the NULLIF function or a CASE expression:

SELECT 
	NULLIF(@DateVariable, CAST('19000101' AS DATETIME)) ,
	CASE WHEN @DateVariable = CAST('19000101' AS DATETIME) THEN NULL ELSE @DateVariable END

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-22 : 11:00:27

Thanks they both worked but the word NULL didn't go into the database it is just blank which is fine with me. Does that still mean it's null?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:46:58

quote:
Originally posted by JJ297

Thanks they both worked but the word NULL didn't go into the database it is just blank which is fine with me. Does that still mean it's null?

it wont be blank as per last suggestion

if you run a query like

SELECT datefiled from table

you should still see them as NULL only in Management studio

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-05-23 : 16:59:49

Yes I do and thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:44:07

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources