Null Values

[dpatfield66]

I've seen some postings on Null Values, but they don't seem to help me.

How can I pass a NULL value to an SQL Table Date Field, if I'm using a Masked Text Box to store the value in the forms?

In other words, if the text of this box is "", how can I get the Insert method to accept as an argument (DBNull)?

Remember, my paramater wants a DateValue. If the string that comes from the Masked Text Box is a valid date, then the parameter will work, but if it's "", the error is: "Cannot convert "" to Date" In this case I just want make the paramater NULL, but DBNull will not be accepted in the paramater. I cannot set any variables to NULL, except I believe the OBJECT type, and when I do that, I get a cast error on the paramater.

Ex:

dim objDate as Object
If Me.txtADate.Text = "" Then
objDate = System.DBNull
Else
objDate = Me.txtADate.Text
End If

But when I say obixAdmitTableAdapter.Insert(objDate), I get the cast error. Because the argument wants a date value.

There is some behind-the-scenes code that I've updated for the Insert Method but everytime I come back at a later date, the code has disappeared and the original code is there. I tried saving and then closing the app, and then re-opening, and the code is there, but just today, for some reason it's wiped out again. Not sure what I do that erases it.

 

[cjard]

what rdbms are you using? oracle? sqlserver?

and is it a date type or a timestamp type column?

 

[dpatfield66]

I'm using SQL server, and I just found out that my Replace() is not working properly.

It doesn't matter what I put as the second argument, SQL will NULL out my field.

date1 = 1/1/1900

Ex: Replace(date1,'1/1/1900',NULL) Field will be nulled
Replace(date1, '1/1/2006,NULL) Field still nulled

so much for that! And I had finally figured out a way to get the 1/1/1900 removed from the times.

time1 = 1/1/1900 02:00:00 AM
I use a variable CONVERT(DATETIME, @datTime,102)

Where:

time1 = CONVERT(DATETIME,'1899-12-30 ' + time1,102)

Don't ask, it works!!! Except when the masked text box for the time = "", in which case I set the variable to 1/1/1900 again....ick!!!

time1 = CONVERT(DATETIME,'1/1/1900', 102)

So, now the best I have is when a time exists in my masked text box, it goes over into sql without the 1/1/1900, but if it's "", then it contains 1/1/1900.

and my dates still have 1/1/1900 when the masked text box = ""

Now I just need an update query that NULLS the field completely when it reads only 1/1/1900, whether time or date.

And I don't want a bunch of single update queries:

Ex:

Update myTable SET date1 = NULL WHERE date1 = '1/1/1900'
Update myTable SET date2 = NULL WHERE date2 = '1/1/1900'
Update myTable SET date3 = NULL WHERE date3 = '1/1/1900'
Update myTable SET date4 = NULL WHERE date4 = '1/1/1900'
etc; etc....

If I could line them up like that in VB code, I wouldn't mind, but this isn't VB Code, it's TableAdapter.UpdateDate1, TableAdapter.UpdateDate2

Pretty cumbersome.

 

[cjard]

Ok, until I can figure out this issue, I've created a temp updatequery that takes any of the dates in SQL that read 1/1/1900 and puts them back to NULL.

Ex: UPDATE [dbo].[OBAdmit]
SET MDOB = Replace(MDOB,'1/1/1900',NULL), and so on...

It works fine.

Now here's the catch...how can I do this for my times?

2 situations... when the time is null, I can do like above, but when it's got a time, I get 1/1/1900 2:00:00 PM for example.

How can I JUST get rid of the 1/1/1900 (in SQL!!!)? I already know how to get rid of it in my .NET forms and reporting, but I'd really like to get it out of the SQL table.

Remember, I need to get rid of 1/1/1900 when the time is null an only shows 1/1/1900 AND I need to just get rid of the 1/1/1900 when there's a valid time in there.

mhhh, i dont think you will be able to you know.. because times in most rdbms are represented like this:

123.4567

which means 123 days and 0.4567 of a day since the epoch. the epoch is an arbitrarily decided time such as 1/1/1900

so suppose we could see the actual number behind the time and date, if it was 1.5 then the field would be 2 jan, at 12 noon (which is 1.5 days after 01/01/1900 00:00)

if you put jsut a time into the field, it will be stored as a number between 0 and 1 i.e. if you put 6am in there it will be stored as 0.25 i.e. a quarter of a day past the epoch and it will ALWAYS have the 01 01 1900 in there because thats when the epoch was

this way date math will work.. you can say date1 - date2 and you get the number of days between them because they are both stored as fractional numbers of days since the same epoch

you either format this out (in oracle i would do to_date(date_field, 'HH24:MI:SS') ) or accept that its there because of the way it is stored

if its all too much of a hassle you may find a better response by storing your dates as 8 wide varchars like:
20050102 = 2005 jan 02

and do conversions on them. its only minorly harder to do date math..



but from what I read, sql server alllows null values in dates and maybe also timestamps, but you need to double check that your defaults and table setup alows this..
i.e. make sure that the sql table alows nulls for that column and that the default value is null.. if its still bugging you let me know and i'll install an instance of sqls and run it - im an oracle boy you see, but its been bugging you that long that i'd run it just to have a play about with it

 

[cjard]

also, please remember that when you say WHERE myTimeField = '1/1/1900' youre relying on sqlserver either converting the time field to a stirng and comparing the string, or guessing how to convert the string '1/1/1900' into a date...

its really not safe, and writing a medical app needs to be more rigid than this.. if you cannot get any satisfaction with date fields i strongly recommend you use strings that represent dates always in YYYYMMDD format (they sort alphabetically == time ordering too) that way

if any math needs to be done on these strings, or formatting, then convert them to a date.

in oracle i would do:

to_char( to_date( myDateAsString, 'YYYYMMDD'), ''DD Mon YYYY')

this will turn a string of 20050101 into a date of 01/01/2005 (internal representation) then back intoa string of '01 Jan 2005'

i can do math too:

to_char( to_date( myDateAsString, 'YYYYMMDD') + 10.5 , ''DD Mon YYYY')

which turns my 20050101 into 01/01/2005, adds ten and a half days to it to be 11/01/2005 12:00 and then back to a date string as '11 Jan 2005'

Note that the time is dropped because in date->string convert i didnt specify a format that allowed the time to persist

 

[cjard]

check also that your data table def in the data set

a) allows nulls, b) has a default value of null

 

[cjard]

uuuuuuuuuuuuuuuuuurrgghhhhhh

i just took a close look at sql server's datetime time and converting it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp


and all i can say is "yuck".. you can have one of 15 or so pre defined date formats with optional 4 digit years?

yuck


store those dates and times as varchars, and when you load them into your vb code, parse them there to do math with them (using DateTime.ParseExact) and convert them back to strings before they go into the database..

sqlserver's support for date conversion alone is so retarded its enough to put me off ever wanting to use the data type.. sorry, i really didnt know it was that bad. oracle jsut went up in my estimation.. (oracle is really very nice with date, as you have seen)

 

[RTaulbee]

Sorry man, for some reason your MSN never popped in my list. What is your MSN, I'll try to add you and see if that does the trick. These types of issues are hard to work through on a forum lol

And sorry for the delay, I'm not getting emails when responses are posted even though I have it set to do so.

 

[cjard]

MSN instant message me at "MaestroSC" It will be good to have some fellow programmers to throw ideas around with and maybe we can get your problem solved by chatting.

er.. MSN msgr uses email addresses as sign in names. are we to assume you mean "at hotmail dot com" on the end of that ID you gave?

 

[dpatfield66]

Sorry, CJARD, you're whole string of replies to my Time issue, just threw me way outta whack. I'm not quite sure where you were headed with that.

When my SQL time reads 1/1/1900 3:00:00 PM, I simply want it to say:

3:00:00 PM

I actually solved this issue, but can't seem to get the following to work:

If I have multiple fields in a record (row) that are NULL, let's say, and I want to update them to be 0 instead, how would I do this? Does anyone know.

I don't want to use multiple update queries, just one.
ONE query that says, hey...if the field entry is NULL, make it 0.

Or if the Date Field is 1/1/1900, make it NULL.

So, if I have 5 Null fields...
or 5 Date fields with 1/1/1900...

I want to update each field above to whatever (0 in the first case, Null in the second)

These don't have to be the same query...I can have one query for the NULL to 0 update, and one query for the 1/1/1900 to NULL update.

But I don't want 5 queries for each NULL field
Or 5 different queries for each date with 1/1/1900

I KNOW this can be done, anyone???

 

[cjard]

When my SQL time reads 1/1/1900 3:00:00 PM, I simply want it to say:
3:00:00 PM

you cant, because sql datetime stores a date and a time as one item. it is stored as the number of days (and fractions of days) since some point in time. for sql server i guess this is 01 jan 1900

so all your dates will have a date part and a time part. if you could see the number behind the date column, you would see for example, a 0 meaning "0 days since 01/01/1900"

because its just a number you cannot remove the date portion of it

if you put a date in of 01/01/1901 i.e. one year after, then sql server would store the date as 365 - three hundred and sixty five days after 01/01/1900

the only way to have sqls look like it is storing a time is to format the date so you only get the time part. i.e. when you pull it out the DB, store it as a VB Date object and say
myDate.Tostring("hhMMss")

If I have multiple fields in a record (row) that are NULL, let's say, and I want to update them to be 0 instead, how would I do this? Does anyone know.

UPDATE myTable SET field1 = nvl(field1, 0), field2 = nvl(field2, 0)
WHERE field1 IS NULL or field2 IS NULL

and so on..

on oracle NVL(vaule, default) takes 2 arguments
value argument = the field that might be null, the value is returned if it is not null
default argument = what to return if it is null

so
NVL('hello world', 0) --> returns 'hello world'
NVL(null, 0) --> returns 0

have a look for a similar command. if you dont have it, you might have something simialr to decodE:

DECODE(field, test_against_this_value, return_this_if_equal, else_return_this)
centre sections can be extended with multiple values:

DECODE(field1, 'one', 1, 'two', 2, 'three', 3, 0)
if field1 is the string 'one' then 1 is returned, 'two' causes 2 to be returned, 'three' causes 3 to be returned, anything else causes 0 to be returned

so in your case:
DECODE(field1, null, 0, field1)

meaning: if field1 is null return 0 else return field1

I don't want to use multiple update queries, just one.
ONE query that says, hey...if the field entry is NULL, make it 0.

you need multiple terms in the update.. se above.
UPDATE table SET col1 = ???, col2 = ???, col3 = ???

you cant get the update query to work on col1 or 2 or 3 separately

Or if the Date Field is 1/1/1900, make it NULL.

putting this in a trigger would be much easier. a trigger is a block of code in the DB (t-sql in your case) that runs every time something happens to a table or row, like update

in oracle i would create a trigger, firing for every row to be updated and say:

if :NEW.someDate = to_date('19000101', 'YYYYMMDD') THEN :NEW.someDate := null;

thats all the trigger has to do.. i'm astounded this cant be done from client code.. shocking behaviour!

So, if I have 5 Null fields...
or 5 Date fields with 1/1/1900...

I want to update each field above to whatever (0 in the first case, Null in the second)

These don't have to be the same query...I can have one query for the NULL to 0 update, and one query for the 1/1/1900 to NULL update.

But I don't want 5 queries for each NULL field
Or 5 different queries for each date with 1/1/1900

I KNOW this can be done, anyone???

use NVL for the null fields, and DECODE for the dates. in oracle it would be:

UPDATE myTable SET numberField = nvl(numberfield, 0), dateField = DECODE(dateField, to_date('19000101', 'YYYYMMDD'), null, datefield)




the to_date('19000101', 'YYYYMMDD') is the only safe way to create a date object.. you CANNOT (in oracle) just pass in a string looking like a date and expect the parser to get it right.. it might work ok in your country and then in another country with a different date format, it breaks, so we are always explicit about this.. in sqlserver your date patterns are fixed to a rather incomprehensive list. i wish you luck with it!

 

[dpatfield66]

Thanks, I was able to take care of this using case statements in my update query:

ex:

Update myTable
SET

date1 = case when date1 = '1/1/1900' then NULL else date1 end case,
date2 = case when date2 = '1/1/1900' then NULL else date2 end case,
etc....

As always, when it works, I don't question it until it breaks later on...

Thanks for the suggestions, though!