[RESOLVED] Date format

[viper5646]

Hi I'm using VS in Vista.
I have using a DataBase in SQL2008 which its date format is year,month,day.
In my app I have the date format as Format(Now, "d") which shows 13/12/2009 but when inserted it displays as 1900-01-01 in the DataBase.
How can I have the date format to dispalay year,month,day as in the database.
I have tried other ways but was not successful.
Can anyone help.
Tahnks

[rplcmint]

I think you have to use FormatDateTime function.....

[jmcilhinney]

Dates do not have formats. What you see in the database, if it really is a date and not text, is simply the format used to display the date, not the format used to store it. Dates are stored simply as numbers. If your dates are not stored correctly in the database then it's because you're not inserting them properly. If you're converting them to text first then that would explain it. Do NOT, I repeat NOT, convert numbers or dates or anything else that isn't text into text to store it in a database. Databases store data in binary format so all your data should remain in binary format when you insert it.

Show us how you're inserting the dates into the database and we'll show you how to fix it. I can assure you that it will not involve Format, FormatDateTime or anything else to do with formatting.

[viper5646]

Thanks for your quick reply.
I have tried the FormatDateTime But still continue unsuccessful I get day/month/year
This is what I have now.

 Public m_Today As String = FormatDateTime(Now, DateFormat.GeneralDate)

I also have tied it with LongDate and ShortDate

[viper5646]

Thanks Jmcilhinney
Here how I'm using my date. I use this code to acquire the date and format it.

[CODE] Public m_Today As String = FormatDateTime(Now, DateFormat.GeneralDate)/CODE]

And then I convert it to date as follows.

  Dim todaydate As Date = CDate(RogersForm.m_Today)

[jmcilhinney]

[CODE] Public m_Today As String = FormatDateTime(Now, DateFormat.GeneralDate)/CODE]

  Dim todaydate As Date = CDate(RogersForm.m_Today)

Perhaps you could refrain from putting your actual comments inside QUOTE tags. QUOTES are for quoting other posts, not your own.

As for the issue, there is absolutely no point, under any circumstances, in converting a Date to a String and back to a Date. Like I said, Dates are binary objects that have no format so you can't possible gain anything by that and you might end up losing data or causing an error. If you want today's date then use today's date:

Dim todaysDate As Date = Date.Today

You then need to insert that value into the database as a binary date, NOT as text. You haven't actually provided us with the code that you use to insert the data into the database as I requested.

[viper5646]

I must be doing something wrong.
Here is my date format
Public m_Today As Date = Date.Today
and this is the code I use to insert.

Dim con As SqlConnection = New SqlConnection(MainForm.SQLcon)

con.Open()

mysql = fnction & Account & " Set [Date]=" & RogersForm.m_Today & ", Description='" & txtDescription.Text & "', [User]='" & RogersForm.User & _
"',Amount=" & amount & " WHERE ID=" & m_selectedutility & ""

Dim com As New SqlCommand(mysql, con)
com.CommandType = CommandType.Text
com.CommandText = mysql
com.ExecuteNonQuery()
con.Close()

[jmcilhinney]

What you're doing wrong is using string concatenation to build SQL statements. That requires that all your binary objects, e.g. dates and numbers, get converted to text. As I said, don't convert anything to text that isn't text. All binary data should remain in binary form and be inserted using parameters. Follow the last link in my signature for the why and the how.

[viper5646]

Hi John
I have read Using parameters in ADO.net and tried it.
I get the following error. "SqlException was unhandled" {"The parameterized query '(@today datetime,@description varchar(250),@users char(10),@amou' expects the parameter '@today', which was not supplied."}
This is my new code.

Private Sub inserttest()

        Dim today As DateTime = RogersForm.m_Today
        Dim fnction As String = "Insert Into "
        Dim con As SqlConnection = New SqlConnection(MainForm.SQLcon)
        con.Open()

        Dim mysql As String = fnction & Account & "([Date],Description,[user],Amount) Values  ( @today, @description, @users, @amount )"

        Dim com As New SqlCommand(mysql, con)
        With com.Parameters
            .Add("@today", SqlDbType.DateTime, 0, "Today")
            .Add("@description", SqlDbType.VarChar, 250, "txtDescription.text")
            .Add("@users", SqlDbType.Char, 10, "RogersForm.User")
            .Add("@amount", SqlDbType.Money, 0, "txtamount.text")
        End With

        com.CommandType = CommandType.Text
        com.CommandText = mysql
        com.ExecuteNonQuery()
        con.Close()
    End Sub

Thanks

[jmcilhinney]

You're creating the parameters but you aren't setting their values. I think you need to go back and read my blog post more carefully. It demonstrates two different scenarios: when you're calling ExecuteNonQuery on a Command and when you're calling Update on a DataAdapter. You're using half of one and half of the other.

[viper5646]

Thanks John
I Finaly got it working with your help