Date changes to a vastly different value when saved into database

Question

I'm trying to enter a date that's in a textbox into a column of datetime type

The code is as follows

    txtbookissue_date.Text = DateTime.Now.Date
    txtbookreturn_date.Text = DateAdd(DateInterval.Day, 7, DateTime.Now.Date)

When I Insert these two values into a database, (insert into book....) values such as 17-02-1984 show up in the database rather than, say, 26-2-2015.

I did a little research and found out that SQL interprets it as "26 minus 2 minus 2015" rather than as a date.

Printing Date(txtbookissue_date.Text) gives correct results, the only problem is saving it into the database.

The solution for this was apparently to enclose the date in single quotes, i.e '26-2-2015' rather than just 26-2-2015, Since I'm using a date function I decided to change

txtbookissue_date.Text = DateTime.Now.Date

to

txtbookissue_date.Text= "'"+DateTime.Now.Date+"'"

but It returns an error, something similar to 'cannot convert varchar type to date type, out of range exception'

How do I fix this? any help would be appreciated.

    txtbookissue_date.Text = DateTime.Now.Date
    txtbookreturn_date.Text = DateAdd(DateInterval.Day, 10, DateTime.Now.Date)

Protected Sub btn_issue_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_issue.Click
    con.Open()
    cmd.CommandText = "insert into Book (book_id, book_name, book_author,publisher,mem_id,mem_name,issue_date,return_date) values('" & txtbookissue_id.Text & "','" & txtibookssue_name.Text & "','" & txtbookissue_author.Text & "','" & txtbookissue_publi.Text & "','" & txtbookissue_memid.Text & "','" & txtbookissue_memname.Text & "'," & txtbookissue_date.Text & "," & txtbookreturn_date.Text & ")"
    cmd.Connection = con
    cmd.ExecuteNonQuery()
    con.Close()
    Response.Redirect("Welcome.aspx")
End Sub

your code will not compile. Show us the code you actually use. — user1666620, Oct 16 '15 at 09:50
show the code you are using to insert data into the database. — user1666620, Oct 16 '15 at 09:52
txtbookissue_date.Text = Date.Now.Date txtbookreturn_date.Text = DateAdd(DateInterval.Day, 7, Date.Now.Date) Do While dr.Read txtbookissue_name.Text = dr("b_name") txtbookissue_author.Text = dr("b_author") txtbookissue_publi.Text = dr("publisher") Loop con.Close() txtbookissue_date.Text = Date.Now.Date txtbookreturn_date.Text = DateAdd(DateInterval.Day, 7, Date.Now.Date) End Sub — StringerBell, Oct 16 '15 at 09:55
txtbookissue_date is a Textbox control? Then why not format your date object with ToString() or String.Format() instead of surrounding it with ' http://www.csharp-examples.net/string-format-datetime/ — Robin Gordijn, Oct 16 '15 at 09:55
@StringerBell edit your code into the question, not into comments. — user1666620, Oct 16 '15 at 09:56
edited the code into the question. @Robin Gordijin, thanks i'll try that, and let you know soon. — StringerBell, Oct 16 '15 at 10:03
Possible duplicate of [Set a database value to null with a SqlCommand + parameters](http://stackoverflow.com/questions/170186/set-a-database-value-to-null-with-a-sqlcommand-parameters) — ivan_pozdeev, Oct 16 '15 at 21:21

score 2 · Accepted Answer · edited May 23 '17 at 12:14

you are inserting date as a text. I mean while you need to insert it '2015-02-26' you are trying to insert it with another format. In fact you should use parameters with your sql query. Your sql statement should be something like that

insert into Book (book_id, book_name, book_author,publisher,mem_id,mem_name,issue_date,return_date) values(@book_id, @book_name, @book_author,@publisher,@mem_id,@mem_name,@issue_date,@return_date)

Before executing query you should set parameters in command object.

cmd.Parameters.AddWithValue("@return_date", DateAdd(DateInterval.Day, 10, Date.Now.Date))

For more information about using parameters with access you can take a look here

Thank you guys, using parameters worked like a charm, @fofik, your code worked, I didn't even have to break a sweat, just copied it and pasted it into my code, and then changed a few things :). — StringerBell, Oct 16 '15 at 10:23

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

First of all, I would highly suggest using Paramaters.

Second, since you want to format your date into a string that is not the default culture. I would suggest you use String.Format() or ToString() (examples).

Since your database most likely expects a datetime. You could parse the string back to a DateTime using DateTime.ParseExact. Look at this answer for a howto.

Let me know if this helps, if not you need to supply us with more info.

edited May 23 '17 at 11:44

Community

1
1

answered Oct 16 '15 at 10:10

Robin Gordijn

683
1
4
16

It seems like I just had to use Parameters for it to work, but I'll keep your examples link bookmarked, may come in handy, thanks. – StringerBell Oct 16 '15 at 10:25

score 0 · Answer 3 · answered Oct 16 '15 at 10:14

You are putting string in a DateTime column, please convert the values back to their original types before putting them in the database.

DateTime issueDate = DateTime.Parse(txtbookissue_date.Text);
DateTime returnDate = DateTime.Parse(txtbookreturn_date.Text);

Date changes to a vastly different value when saved into database

3 Answers3