SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding N/A to database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/21/2012 :  19:41:04  Show Profile  Reply with Quote
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)

Singapore
16746 Posts
Posted - 05/21/2012 :  19:44:53  Show Profile  Reply with Quote
No. You can't store text into a date field



KH
Time is always against us
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 05/21/2012 :  19:52:41  Show Profile  Reply with Quote
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/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/21/2012 :  19:57:47  Show Profile  Reply with Quote
Thanks I need to maintain the datefield so the 1900 date will have to go into the database. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 05/21/2012 :  20:04:56  Show Profile  Reply with Quote
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/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/21/2012 :  20:42:00  Show Profile  Reply with Quote
Okay thanks again I will!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 05/21/2012 :  21:07:00  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 05/22/2012 :  06:44:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is wrong with NULL? You can leave it to have NULL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/22/2012 :  09:02:45  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 05/22/2012 :  10:29:17  Show Profile  Reply with Quote
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
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/22/2012 :  11:00:27  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 05/23/2012 :  15:46:58  Show Profile  Reply with Quote
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/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 05/23/2012 :  16:59:49  Show Profile  Reply with Quote
Yes I do and thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 05/24/2012 :  15:44:07  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000