about datetime for insert to database

[Jupping]

when i want to insert datetime to database
sometime is good but sometime is error
this is code

datetimesales = format(now,"dd/MM/yyyy hh:mm")

what's happen?

[jmcilhinney]

There should never be an issue inserting dates into databases because they should be inserted the same way every other value is inserted and every other value should be inserted using parameters. When you start converting values to strings and inserting them into another string you open yourself up to all sorts of issues, this among them. Just use parameters and all those problems go away, e.g.

Dim command As New SqlCommand("INSERT INTO MyTable ([Date]) Values (@Date)", connection)
 
command.Parameters.AddWithValue("@Date", Date.Now)

That's what you should ALWAYS be doing to insert variables into SQL statements.

[si_the_geek]

I entirely agree with that, but thought you might like to know the reasons for the original failure.

The problem is that you made an assumption which is common, and entirely wrong - that the database uses the date format you have specified somewhere (in Control Panel, or within the database system, etc), and it does not. Internally dates do not have a format at all, and via SQL statements the database system has its own specific format that you need to adhere to (the format you used is not right for any database system I've ever seen). While you were providing the date in the format dd/MM/yyyy, it was being interpreted using an entirely different format.

If you use parameters as shown above, this is one of many problems that goes away (others include the ' character in text fields). For further info see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).