Convert value from string to date format in SQL Server 2008

Question

I want to convert date which is stored as string in a SQL Server 2008 database to smalldatetime.

The format for the saved string is 16/12/2007 and I want to remove / and replace it with - to get proper date format which is 16-12-2007

I getting the following error

Conversion from string "16/12/2007" to type 'Date' is not valid.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Conversion from string "16/12/2007" to type 'Date' is not valid.

Source Error:

Line 34: NewsItem.Visible = True
Line 35: NewsItem.Date_Modified = CDate(GetContent.Ndate)
Line 36: NewsItem.Date_Published = CDate(GetContent.Ndate)

I thought of creating a function that replace the / character with - to then update the database but it will take long time.

If you could avoid storing the dates as strings in the first place, the problem should never come up. SQL Server has perfectly usable date and datetime2 data types, which ADO.NET knows how to translate to and from .NET DateTime types. You only encounter formatting issues if you deviate from this and use inappropriate storage types. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 22, 2013 at 9:02

Tim Schmelter · Accepted Answer · 2013-11-22 09:08:16Z

1

Don't use strings if you actually want to store datetime/smalldatetimes. Use sql-parameters to avoid localization/format issues and - more important - to prevent sql-injection. A VB.NET Date can be used for a smalldatetime column. Use Date.TryParse to validate and parse the string.

Dim sql = "INSERT INTO dbo.TableName(Date_Modified)VALUES(@Date_Modified);SELECT CAST(SCOPE_IDENTITY() AS INT);"
Using con = New SqlConnection("Connection-String")
    Using cmd = New SqlCommand(sql, con)
        Dim dt As Date
        If Not Date.TryParse(GetContent.Ndate, dt) Then
            MessageBox.Show("please enter a valid date")
            Return
        End If
        cmd.Parameters.AddWithValue("@Date_Modified", dt)
        Dim newID = cmd.ExecuteScalar() ' presuming that you have an identity column that is autoincremented
    End Using
End Using

answered Nov 22, 2013 at 9:08

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

Comments

Linga · Accepted Answer · 2013-11-22 09:11:06Z

0

Retrieve the date as string from database and then use Date.ParseExact which Converts the specified string representation of a date and time to its DateTime equivalent.

Dim ydate = "16/12/2007"
Dim edate As Date = Date.ParseExact(ydate, "dd/MM/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)

edited Nov 22, 2013 at 9:11

answered Nov 22, 2013 at 9:03

Linga

10.6k10 gold badges57 silver badges109 bronze badges

Collectives™ on Stack Overflow

Convert value from string to date format in SQL Server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related