-2

I need a date to insert into a database, however even though Im formatting it using 

Exportedtime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")

it still shows up as 3/24/2016 09:25:13 AM? So when I go to insert it into the MSSQL database Im getting the error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The Database field is a datetime field.

Any ideas?

Thanks

Brownd92
  • 75
  • 1
  • 11
  • http://stackoverflow.com/questions/1111646/conversion-of-a-varchar-to-a-smalldatetime-results-in-an-out-of-range-value – mohan111 Mar 24 '16 at 09:37
  • Thanks @mohan111 Ive already tried that. – Brownd92 Mar 24 '16 at 09:39
  • Use ISO format like `yyyy-MM-dd HH:mm:ss` instead of `/` – TSungur Mar 24 '16 at 09:39
  • 1
    A date has no format. Use a parameterized insert statement and you don´t need any ToString at all. – Alex B. Mar 24 '16 at 09:41
  • "it still shows up as 3/24/2016 09:25:13 AM" how does it show up? If it is in the debugger, that's OK, because it is simply the debugger's representation – Ian Mar 24 '16 at 09:42
  • Hi Ian, yes its in the Debugger but Im still getting the error. – Brownd92 Mar 24 '16 at 09:46
  • @Brownd92 could you post the code line which gives you the error (as well as some other necessary lines)? In that case, the error might be caused by different error source. – Ian Mar 24 '16 at 09:49

3 Answers3

0

Hi guys thanks for the help. @Alexb provided the hint I needed to carry it out.

con = New SqlConnection(constring)
con.Open()
Dim cmd As New SqlCommand("Update Scripts set ExportedOn = @exportTime where scriptID = " & scriptID, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@exportTime", SqlDbType.DateTime).Value = time
cmd.ExecuteNonQuery()
con.Close()

Thanks for all the help.

Brownd92
  • 75
  • 1
  • 11
-1

I too faced this format issue in my application. After a detailed research on date time format in MSSQL, i came up with this solution. 

"CONVERT(datetime,'" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "',112)"

Use the above string to insert date time value.

Read more on Convert function here - https://msdn.microsoft.com/en-in/library/ms187928.aspx

Mohan Rao
  • 46
  • 8
  • Thanks Mohan, Ive tried that and getting the error : Conversion from string "CONVERT(datetime,'2016/03/24 09:" to type 'Date' is not valid. – Brownd92 Mar 24 '16 at 09:49
  • Try converting datetime object to string without separator and give format specifier as 112. You can use the same code provided in the above answer. – Mohan Rao Mar 24 '16 at 09:52
  • Don't treat date/time data as a string!!!! Use a parameterized query and use DateTime columns in the database – Chris Dunaway Mar 24 '16 at 15:10
-2

here is the simple way you can get :) 

Dim d As String = "10/05/2016 14:30:06"
MsgBox(CDate(d).ToString("yyyy/MM/dd HH:mm:ss"))
IFON26
  • 8
  • 3
  • people so stupid that cant accept new things i work with this method anywhere – IFON26 Mar 24 '16 at 10:20
  • 2
    Only because you work with this method anywhere doesn´t make it a good solution. You provided a code snippet which does not help the question starter. Additionaly it might be found by others and could be taken as a common approach - which it isn´t. Parameterized SQL queries are there for a good reason. – Alex B. Mar 24 '16 at 10:27
  • i use this even with mysql commands why i would love to add extra line in my code and make it complicated ? – IFON26 Mar 24 '16 at 10:36
  • 1
    Its inefficient to convert a string to a date and then back to a string again, when all along you can just supply the date. What happens in your solution when you run on a locale where the date format is `dd/MM/yyyy` and `MM/dd/yyyy` – Matt Wilko Mar 24 '16 at 13:06