Format dates in sql

Asked

Viewed 808 times

1

I have a program in c# (Visual Studio) of registration that contains a textbox to receive a date. Knowing that SQL records the date in American format, how can I make the user to enter the date in Brazilian format and this date can be saved successfully in the database? As I am doing, the user type the date in the Brazilian format, but an error occurs when saving, because SQL is trying to save MM/dd/yyyy. Could someone help me, please? I thank you in advance.

Below the code I’m using:

//Variável que recebe o cast no textbox 
var validaDataVenda = DateTime.Parse(txtDataVenda.Text); 
var validaDataPagto = DateTime.Parse(txtDataPagamento.Text); 

//Comando que insere os dados no banco, recebendo os textboxes como parâmetros
VendaDAO.InsertVenda(novoCodigo.ToString(), txtValor.Text, dataDaVenda.ToString(), dataDoPagamento.ToString(), txtCodigoCliente.SelectedValue.ToString(), txtCodigoProduto.SelectedValue.ToString(), txtQuantidadeRequerida.Text, recebido);

If I insert "31/102016", for example, this error occurs: "Conversion failed when Converting date and/or time from Character string".

c# sql datetime

  • How is the connection between the application and SQL being made? Entity Framework? ADO.NET?

  • When you pass the date to the routine you insert into the database, you use the textbox string directly or cast to datetime?

  • @Andréfigueiredo ADO.NET.

  • @Intruder I do straight from the textbox.

  • Cast to datetime and pass the datetime to the parameter that will be used in Insert/update. Storage (db) is storage, GUI and formatting do not need to be represented in the same way in db.

  • @Intruder keeps giving error :( The problem is that sql only accepts if it is MM/dd/yyyy. But the user will type dd/mm/yyyy in the textbox.

  • Paste the code that takes the value of the screen and the code that makes the Insert here. Also put the error that SQL returns, the value that represents the date of a Datetime is internal and does not depend on the formatting. If you could cast it, it should work.

  • //Variável que recebe o cast no textbox
var validaDataVenda = DateTime.Parse(txtDataVenda.Text);
var validaDataPagto = DateTime.Parse(txtDataPagamento.Text);

//Insere no banco recebendo os textbox como params
VendaDAO.InsertVenda(novoCodigo.ToString(), txtValor.Text, dataDaVenda.ToString(), dataDoPagamento.ToString(), txtCodigoCliente.SelectedValue.ToString(), txtCodigoProduto.SelectedValue.ToString(), txtQuantidadeRequerida.Text, recebido);

Se eu inserir "31/102016", por exemplo, ocorre este erro:
"Conversion failed when Converting date and/or time from Character string".

  • @Intruder I edited my question to improve the visualization of the code.

  • Why do you need to do dataDaVenda.Tostring() ? You test if the field value is null before casting?

Show 5 more comments

1 answer

0


Although the way you are passing the parameters is not the most indicated, you should send the data in your data types without "tostring" and write according to the method used below.

But if you want to send in text format, better take a look at this article, which explains how you format a date correctly.

https://stackoverflow.com/questions/5050102/convert-datetime-to-date-format-dd-mm-yyyy

Now I do not know how your application, for Web I do not recommend Inserts in the database without using parameters as in the method below to avoid Sql Injection.

Here is an example of a function that inserts dates that solves your problem

public Boolean Insere(Clientes cadastro)
{
    Conexao banco = new Conexao();
    SqlConnection con = banco.Conectar();

    try
    {
        SqlCommand comando = new SqlCommand(@"INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE, DATANASC) 
            VALUES (@nome, @email, @telefone, @datanasc)", con);

        con.Open();
        comando.Parameters.AddWithValue("@nome", cadastro.Nome);
        comando.Parameters.AddWithValue("@email", cadastro.Email);
        comando.Parameters.AddWithValue("@telefone", cadastro.Telefone);
        comando.Parameters.AddWithValue("@datanasc", cadastro.DataNasc);

        comando.ExecuteNonQuery();
        return true;

    }
    catch (Exception erro)
    {
        return false;
    }
}
  • 1

    Dude, that’s exactly what it was!!! Thank you so much.

Browser other questions tagged c# sql datetime

You are not signed in. Login or sign up in order to post.