SQL datetime to string format

Question

I have some code which declares an object variable, and this variable is assigned a value from an existing database field.

Dim datestart As Object
datestart = dbToDate(dr("DateStart"))

The variable is then passed through a function which checks whether or not it is null, and then converts it into datetime data type.

     Public Shared Function dbToDate(o As Object) As DateTime

    If o Is DBNull.Value Then
        Return Nothing

    Else
        Return Convert.ToDateTime(o)

    End If
End Function

The last thing I need to do with it is convert it into a date formatted string, DD/MM/YYYY, so that I can insert it into a new database.

The function that I have so far is

Public Shared Function sqlDate(dt As DateTime) As String

    Return "'" & Format(dt, "yyyyMMdd") & "'"

End Function

However, when I run the code, I get the following error message

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Why is this, and how do I fix it?

Check the values in your table for the DateStart column. One or more rows contain values which are out of range specified for c# DateTime type. — The Shooter
– The Shooter, Commented Jul 29, 2016 at 8:49
Sorry, I forgot to note the language but language is irrelevant. Underlying data type of all .net languages are same. Put a breakpoint on datestart = dbToDate(dr("DateStart")) statement, check value of dr("DateStart") and see if it lies between DateTime.Mix and DateTime.Max. Alternatively note down the value of DateTime.Min and DateTime.Max and in the sql table find rows that have value out of this range. — The Shooter
– The Shooter, Commented Jul 29, 2016 at 8:57
Ypu probably don't need to convert the DateTime to a string, instead look at parametrising your queries. — Rowland Shaw
– Rowland Shaw, Commented Jul 29, 2016 at 9:28

Sankar · Accepted Answer · 2016-07-29 08:58:18Z

1

Simply, you can use toString function. Also check your timezone. The problem might be in the time zone setting.

Hope you are passing date as string to database, Try to pass date as Datetime variable rather than string

Public Shared Function sqlDate(dt As DateTime) As String

    Return dt.toString("yyyy-MM-dd")

End Function

For your Ref: https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

edited Jul 29, 2016 at 8:58

answered Jul 29, 2016 at 8:42

Sankar

7,1772 gold badges36 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6634034 Over a year ago

Sorry Sankar, my mistake, it's because I'd added hhmmss tt at the end of the string. However, one of my dates is null, and in the function called dbToDate (see question), instead of returning 'Nothing' it returns 12:00:00AM, so this function won't work to insert it into the database. How do I get this to work with these?

Sankar Over a year ago

@joe So you are concatenating time with date. right?

user6634034 Over a year ago

I want the value to just be a date. In the database, there are 4 datetimes. 3 representing dates without times, the 4th representing a time without a date. 1 date and the time field allow nulls. With the time field, this works fine as it is converted to Nothing. However, the null date is converted to a time, which is causing the issue

Collectives™ on Stack Overflow

SQL datetime to string format

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related