how to convert date from string to date in vb.net?

Question

I have date in string format like "14-12-2012". After converting the date from string to date it is giving like this "#12/14/2012#"

Then I am trying to save date in database it is giving error.

Dim queryString As String = "UPDATE [ARIBA_CUST_ADMIN] SET [CUST_CRED_ID] = '" + eCustomer.CUST_CRED_ID + "',[CUST_NAME] = '" + eCustomer.CUST_NAME + "',[STREET1] = '" + eCustomer.STREET1 + "',[STREET2] = '" + eCustomer.STREET2 + "',[CITY] = '" + eCustomer.CITY + "',[State] = '" + eCustomer.STATE + "',[POSTAL_CD] = '" + eCustomer.POSTAL_CD + "',[COUNTRY] = '" + eCustomer.COUNTRY + "',[CUST_CRED_DOMAIN] = '" + eCustomer.CUST_CRED_DOMAIN + "',[SUBCHARGE] = '" + eCustomer.SUBCHARGE + "',[TAX_AT_LINE] = '" + eCustomer.TAX_AT_LINE + "',[PO_FLIP] = '" + eCustomer.PO_FLIP + "',[STATUS] = '" + eCustomer.STATUS + "',[CONFIMRATIONS] = '" + eCustomer.CONFIMRATIONS + "',[SHOP_NOTICES] = '" + eCustomer.SHOP_NOTICES + "',[INVOICE_TYPE] = '" + eCustomer.INVOICE_TYPE + "',[EFFECTIVE_DATE] = " + eCustomer.EFFECTIVE_DATE + ",[DISTRIBUTION_XML] = '" + eCustomer.DISTRIBUTION_XML + "' WHERE [CUST_ID] = " + eCustomer.CUST_ID

It just looks so in the debugger, it is a normal `Date`. So the reason for your error must be somewhere else(f.e. the sql itself). — Tim Schmelter, Dec 14 '12 at 10:13
I agree with Tim, this sounds like a problem with the way you insert the date into the database. Show us your INSERT/UPDATE code! — Heinzi, Dec 14 '12 at 10:20
Please paste all the code related to the question, where you get/set the date etc.. — Ryan McDonough, Dec 14 '12 at 10:47

Ryan McDonough · Answer 1 · 2012-12-14T10:11:02.713

1

Dim dateString, format As String
        Dim result As Date
        Dim provider As Globalization.CultureInfo = Globalization.CultureInfo.InvariantCulture

        ' Parse date and time with custom specifier.
        dateString = "20121216"
        format = "yyyyMMdd"


        result = Date.ParseExact(dateString, format, provider)

This will output the date in whatever format you specify in the format variable.

For the date format you want you'd use:

format = "dd-MM-yyyy"

edited Dec 14 '12 at 10:11

answered Dec 14 '12 at 10:03

Ryan McDonough

9,732
3
55
76

You can, your database should convert it during the query as long as the string you send is a valid date. Give it a go. – Ryan McDonough Dec 14 '12 at 10:16
I used your code but it is giving error "String was not recognized as a valid DateTime." My date in string is "14132012" and i used format like "dd-mm-yyyy" – Srinivas Dec 14 '12 at 10:18
Hold on, is your database field a date or a datetime? – Ryan McDonough Dec 14 '12 at 10:19
What is your code for getting the date? Add it in your question,. – Ryan McDonough Dec 14 '12 at 10:46
2

@user1887347 - If your date string is "14132012", how can that possibly be a valid date? Is the day 14 and the month 13? – Chris Dunaway Dec 14 '12 at 15:26
@user1887347 chris is correct, your date is incorrect to begin with. – Ryan McDonough Dec 15 '12 at 17:00

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

You should use a parameterised query then you don't need to worry about the specific format, you just pass a date as the parameter (Assuming your DB column is a date type - it should be if it isn't):

Something along these lines

Dim d as Date= new Date(2012, 12, 3)'or ParseExact from a string in a known format
Dim sql As String = "INSERT INTO foo (DateValue) VALUES (@DateValue)"

Using cn As New SqlConnection("Your connection string here"), _
    cmd As New SqlCommand(sql, cn)

    cmd.Parameters.Add("@DateValue", SqlDbTypes.Date).Value = d
    cmd.ExecuteNonQuery
End Using

Have a look at this question for more info

edited May 23 '17 at 10:24

Community

1
1

answered Dec 14 '12 at 10:22

Matt Wilko

26,994
10
93
143

i am getting result like this "#12/13/2012#" because of # symbol the data is not saving sql server – Srinivas Dec 14 '12 at 10:31
1

@user1887347 - You say you convert to date, then to string, just omit the conversion to a string and use the date instead – Matt Wilko Dec 14 '12 at 11:01

how to convert date from string to date in vb.net?

2 Answers2