0
I have a table that one of the fields is date type. On my page insert.Asp when I type the date for example so 10/07/2017 or 2017-07-12 not saved in Mysql database.
The error that is shown is this:
Error: [Mysql][ODBC 3.51 Driver][mysqld-5.7.13-log]Incorrect date value: '' for column 'data_start' at Row 1
I created a function in the HTML code:
<%
Function InverteDataMySql(data_inicio)
if not isdate(data_inicio) then exit function
if instr(data_inicio, " ") > 0 then
hora = split(data_inicio, " ")(1)
end if
InverteDataMySql = year(cdate(data_inicio)) & "-" & month(cdate(data_inicio)) & "-" & day(cdate(data_inicio))
if hora <> "" then InverteDataMySql = InverteDataMySql & " " & hora
End Function
%>
I call the function here on the insert page.:
<tr>
<td>Data Inicio:</td>
<td>
<input name="FormDataInicio" size="10" maxlength="80" value="<%=Response.Write(InverteDataMySql())%>"/>
</td>
</tr>
But it doesn’t work.
Everything has been solved in SQL itself using STR_TO_DATE
The code went like this:
"INSERT INTO tb_registros_para_controle(nome_livro, nome_autor, nome_editora, data_inicio, "
sql = sql & " data_termino, data_paralisacao, "
sql = sql & " segunda_feira, terca_feira, quarta_feira, quinta_feira, "
sql = sql & " sexta_feira, sabado, domingo, observacao) VALUES ( "
sql = sql & "'" & Request.Form("FormNomeLivro") & "', "
sql = sql & "'" & Request.Form("FormNomeAutor") & "', "
sql = sql & "'" & Request.Form("FormNomeEditora") & "', "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataInicio") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataTermino") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataParalisacao")&"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataSegunda") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataTerca") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataQuarta") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataQuinta") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataSexta") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataSabado") &"','%d/%m/%Y')" & ", "
sql = sql & "STR_TO_DATE('" & Request.Form("FormDataDomingo") &"','%d/%m/%Y')" & ", "
sql = sql & "'" & Request.Form("FormNomeObservacao") & "' "
sql = sql & ")"
The correct format is
AAAA-MM-DD
. You are pasting the date in this format and in quotation marks?– Roberto de Campos
David, thank you for your attention, but I’m sorry, I don’t understand.
– Teixeira
Complementing: David, thanks even for the attention, Regarding your question I did not understand, sorry. I cannot find an HTML/ASP example to record a date in the date or datetime field in the database in Mysql. I believe I’m mistaken in calling the function that Gustavo suggested to me. In the book I am studying only example save, change and delete text field. David, I’ve been struggling for days to learn how to save, change a record in the Mysql database by interacting HTML with ASP. Thank you again for your attention.
– Teixeira