vb.net Date values being converted to time

Question

I have the following code to check whether or not a datetime value is null or has a value. If it's null, I want it to be returned as a blank string, else it should be a string of the value it contains.

The Else part of the statement works, it converts the value into a string, however, if the value being passed in as a datetime is null, it doesn't return Nothing, it instead sets the value to '12:00:00 AM`, which is causing me problems elsewhere in the project.

How can I adapt this function to make null datetime values return as blank strings?

Public Shared Function dbToDate(o As Object) As DateTime

    If o Is DBNull.Value Then
        Return ""

    Else
        Return Convert.ToDateTime(o)

    End If
End Function

Please tell me you're not going to put this into an SQL string? Because that would be **awful**. It's a _very_ strong indication you're building code that will be crazy vulnerable to sql injection attacks. — Joel Coehoorn, Jul 29 '16 at 14:19
How is this different than [your last question](http://stackoverflow.com/q/38654241/1070452)? ...and Dates still dont need to be converted to strings for SQL. — Ňɏssa Pøngjǣrdenlarp, Jul 29 '16 at 14:20
@JoelCoehoorn No, this isn't going into an SQL string, this is taking data from the database using SQL, but not going back in (just yet) — , Jul 29 '16 at 14:21
Looking at the question that was linked in the other comment, this is _exactly_ what you are doing :( **DON'T BUILD SQL STRINGS LIKE THAT!** It's practically begging to get hacked. — Joel Coehoorn, Jul 29 '16 at 14:23
@Plutonix that question was asking for he syntax that lead to this error being detected, this is how to solve the problem. Also that question is kinda redundant now, as this is no longer being used in an insert statement — , Jul 29 '16 at 14:24
@JoelCoehoorn No, that other question was my initial idea, the way I'm designing the program now has changed so whilst I need the same function, it isn't for the same purpose. This function will later on lead to an INSERT, but I'll be using a paramterised one — , Jul 29 '16 at 14:25
Okay... but the parameter for the insert should use a `DateTime` object rather than a string. Trust me on this. For that matter, I bet you could rewrite things so that the SELECT from the other question and the eventually INSERT combine into one query, such that the data never has to cycle through your client program. — Joel Coehoorn, Jul 29 '16 at 14:26
@JoelCoehoorn I know, and it will do, there are lots of things that the data from this table is needed for, this string conversion malarkey is just one of about 7 things it's used for! — , Jul 29 '16 at 14:28
@JoelCoehoorn Yeah I'm gunna do it using TSQL when it comes to the time of writing the INSERT — , Jul 29 '16 at 14:29

Tim Schmelter · Accepted Answer · 2016-07-29T14:46:48.790

0

First, a DateTime is never null/nothing. Since it's a value type(Structure) it will always have a value. In this case the default value is DateTime.MinValue. But VB.NET handles this case for you. The dt.Date = Nothing will automatically compare the value with Date.MinValue so it's working as desired.

But the problem is that you return dt.ToString. Just return "":

Public Shared Function sqlDate(dt As DateTime) As String    
    If dt.Date = Nothing Then  
        Return ""    
    Else    
        Return dt.ToString("yyyy-MM-dd")    
    End If    
End Function

Here's a version without method:

Dim result = If(dt = Nothing, "", dt.ToString("yyyy-MM-dd"))

You can see that a Date is never null with this code:

Dim dt As Date = Nothing
Console.WriteLine( dt = Nothing )' true
Console.WriteLine( dt = Date.MinValue )' true
Console.WriteLine( Object.ReferenceEquals(Nothing, dt) )' always false because it's a value type

Update acc. your latest edit, compare with DbNull.Value. I'd also return a Nullable(Of Date):

Public Shared Function dbToDate(o As Object) As DateTime?
    If DbNull.Value.Equals(o) OrElse o Is Nothing Then
        Return Nothing
    Else
        Return Convert.ToDateTime(o)
    End If
End Function

edited Jul 29 '16 at 14:46

answered Jul 29 '16 at 14:20

Tim Schmelter

450,073
74
686
939

VB.Net `Nothing` is not the same as C# `null`. DateTime values in VB.Net can be Nothing, which is also equiavalen to C#'s `default(T)` – Joel Coehoorn Jul 29 '16 at 14:21
@JoelCoehoorn: true and false. The value will not be nothing but `Date.MinValue`. But if you would compare it to `Nothing` you would get a `True`. You can also assign `Nothing`. Then (in C# syntax) you will assign `default(DateTime)` which is `Date.MinValue`. I just wanted to point out that a value type is never `null` or nothing(in the same meaning). OP is using both terms so it's important to mention the difference in VB.NET (as you did). Here is a related question. http://stackoverflow.com/questions/15524045/why-is-there-a-difference-in-checking-null-against-a-value-in-vb-net-and-c – Tim Schmelter Jul 29 '16 at 14:27
@TimSchmelter Hi Tim, firstly thanks for clearing up the difference. Secondly, this function is still converting the nothing values to 12:00:00 AM? – Jul 29 '16 at 14:37
@joe: that cannot be, have you used the debugger to see what happens? – Tim Schmelter Jul 29 '16 at 14:38
@TimSchmelter After re-building the solution, it has sorted itself out, however now it just throws the error "Conversion from type string to date is not valid" – Jul 29 '16 at 14:41
@TimSchmelter See updated question, the function I posted for checking if it is null was the wrong one, this is what I have now, and this is throwing the error – Jul 29 '16 at 14:44
@joe: you have to compare with `DbNull.Value`. I would use a nullable type for the case that it's really undefined. However, you can also return `Date.MinValue`. – Tim Schmelter Jul 29 '16 at 14:50

vb.net Date values being converted to time

1 Answers1