[RESOLVED] null dates - actually required

[dale_albiston]

hi.

i have a database that stores a date and a time (seperatly for various reasons) in a pair of date time fields.

it is possible, indeed probable that these fields may be null (either or both) which is not a problem, representing "I don't know" answers.

within the database this is not a problem

moving to vb.net however the dates & times map to the 'system.datetime' data type very well with one problem. It can't handle nulls.

'system.datetime' *always* returns a valid date, thus when i load it with a null value it returns its default. problem is i need a way of assigning a null date, so when i update the database i can leave null values alone, or assign a null value.

so far the only way i can see to do this is to create my own datetime class with an 'isValid' flag i can set & use, or to use 'magic' values for null which i'd rarther avoid since it will screw up a whole range of reports the database runs.

i've tried setting

myDate = nothing

&

if myDate.equals(nothing) then ...

to no avail.

there must be a way of doing this, and its got to be simple but i can't quiet see it.

i've also tried

x = dbnull
if mydate.equals(x) then ...

which also doesn't work..

ARGH....

[dannywooly]

in our application we have a mindate set to 1960 midnight

[dale_albiston]

i know a magic value could be used, but since the database stores 'null' happily i'd like to map this directly to a value. basically since checking something like

if myDate is Null then...

is reasonably clear, i specifically *don't* want magic values int he database, since then everything will need to be setup to filter them out.

also since the time is stored seperatly (making it easy to search for events in the morning for example) all time values are valid.

another feature is the fact that generally the times are never changed once entered, but nulls can b changed as the time is found out. thus another check, which again is more logical on a 'null'

[dannywooly]

Fair enough, just how we built it i guess!

[jmcilhinney]

The .NET DateTime is a structure, therefore a value-type, therefore must always have a value. It is absolutely not possible to have a DateTime object that IS Nothing. If you need that functionality then the easiest way would be to create a class that has just one property that is a DateTime structure. Now that you have boxed the structure in a class you can have a genuine null reference, just like other reference-types, if that's what you want.

[mendhak]

The magic value, I think, is #12:00:00 AM#.

Set the value to that, and when inserting into the database, replace #12:00:00 AM# with DBNull.

[dale_albiston]

looks like a new class is the way to go.. problem with magic years is minimal, there will be nothing in 1900 so thats sort of ok (i just don't like magic numbers) with times *all* times are possible.

so new class it is.. ho hum, just used to playing with pointers. I wonder...

not got vb sat in front of me, but is it possible to have a *reference* to a date time and set it to a value as required, then the referecne could be 'nothing' or a valid value. there is never a requirement for the actual date-time to be null, just the reference to it.

hmmm..

have to look into that.

other problem with magic numbers, theres just gunna be to many places it would need checking, display boxes etc tis somewhat easier with nulls.

thanks anyways though, me post an update when me got this one nailed down.

[jmcilhinney]

not got vb sat in front of me, but is it possible to have a *reference* to a date time and set it to a value as required, then the referecne could be 'nothing' or a valid value. there is never a requirement for the actual date-time to be null, just the reference to it.

That's exactly the difference between a class and a structure, which is why I suggested a class that contains the DateTime structure. The only other way would be to use an Object variable, which could be Nothing, and box your DateTime by assigning it to that. It would require casting whenever you wanted to use it though.

[dale_albiston]

i assume i can make my date class *very* simple, by having one public member i.e. a system.datetime, and then be able to access all parts of the datetime ala

(assuming the custom class contains a datetime member called 'x')

dim dDate as myDate
 
...
 
' ** is the date valid?
if dDate is nothing then
   ' do whatever we do with a null date
else
   ' do whatever we do with a non-null date
   msgbox(ddate.x.toString)
end if

this basically will do what i need, plus i can always create my own .ToString method in my class to get a default formatted string back, and use x.ToString to get the system default.. hmm..

if this works me happy. will try it later..

thanks to ya all :-)

[jmcilhinney]

If you need that functionality then the easiest way would be to create a class that has just one property that is a DateTime structure.

...

[dale_albiston]

ok a small custom class built, and used.. a bit of a cludge but it works

such tis life :-)