How can I insert a date and time into SQL server

Question

1.00/5 (1 vote)

See more:

Hi, I have a problem in C # to save the date and time in sql server

I get the date of a datetimepicker until everything is fine but when I run the procedure to the maceando I get the error "Error converting data type varchar to datetime."
And I tried changing the date settings and it only allows me to save the date

What I have tried:

private void Btn_Aceptar_Click_1(object sender, EventArgs e)
 {
string cmd = string.Format("Execute SpInsertar_Cliente '{0}','{1}','{2}','{3}'", 
this.Txt_Id_Cliente,
this.Txt_RasonSocial.Text.Trim(),
// parametro 3 con error
////////////////////////////////////////////////////////////////////////////// 
// Prueva 1 no funsiona
// CONVERT(DATETIME,'" & Format(FPEDIDO, "yyyy-mm-dd hh:mm:ss") & "',102) , "
// Prueva 2 no funsiona                                                     
// Convert.ToDateTime(DTP_Aniversario.Value, "yyyy-mm-dd hh:mm:ss"),
// Prueva 3 no funsiona     
// DTP_Aniversario.Value.ToString("m",CultureInfo.CreateSpecificCulture("en-us")),
// Prueva 4 funsiona esta generado una clase pero con la hora -> 00:00:00 
Validacion.fechachange(DTP_Aniversario.Value),
////////////////////////////////////////////////////////////////////////////////

this.CkB_Bloqueado.Checked,
);
Utilidades.EjecSql(cmd, Frm_Login.Conexion);
Mensajeok("Se ingreso correctamente el registro");
}

//Clase para validar la fecha
////////////////////////////////////////////////////////////////////////
public class Validacion 
{
       public static string fechachange(DateTime fecha)
        {
            return fecha.ToString("yyyy-MM-dd HH':'mm':'ss");
        }
}

Posted 16-Mar-17 16:19pm

Member 13064453

Updated 17-Mar-17 4:22am

Add a Solution

Comments

Bryian Tan 16-Mar-17 23:06pm

why there are single quotes in this line?

return fecha.ToString("yyyy-MM-dd HH':'mm':'ss");

This should be ok. and might solve your issue too.

return fecha.ToString("yyyy-MM-dd HH:mm:ss");

Prateek Dalbehera 17-Mar-17 1:34am

Can you please ask in English?? It seems that there is some data type mismatch.

Member 13064453 17-Mar-17 23:48pm

I was trying with this senses in the classe "return date.ToString ("yyyy-MM-dd HH: mm: ss");"
But it's returns "2017/03/17 00:00:00.000"
and i need
"2017/03/17 10:30:05"

Sunasara Imdadhusen 17-Mar-17 2:36am

Where is code for saving date in database?

Richard Deeming 17-Mar-17 10:41am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-03-17T04:22:00

As Bryian Tan has indicated the line

C#

return fecha.ToString("yyyy-MM-dd HH':'mm':'ss");

is incorrect. It could be

C#

return fecha.ToString("yyyy-MM-dd HH:mm:ss");

or potentially

C#

return fecha.ToString("u");

if you use the Standard Date and Time Format Strings[^]

However, instead of constructing the command using string.format (which can leave you susceptible to sql injection attacks) why not utilise SqlParameters (SqlParameter Class (System.Data.SqlClient)[^])
Something like ...(untested)

C#

var cmd = New SqlCommand("Execute SpInsertar_Cliente @Client, @RasonSocail,  @Aniversario, @Bloqueado");
cmd.Parameters.AddWithValue("@Client", this.Txt_Id_Cliente);
cmd.Parameters.AddWithValue("@RasonSocail",this.Txt_RasonSocial.Text.Trim());
cmd.Parameters.AddWithValue("@Aniversario", DTP_Aniversario.Value);
cmd.Parameters.AddWithValue("@Blqueado",this.CkB_Bloqueado.Checked);

DELPIN SUSAI RAJ · Answer 2 · 2017-03-16T20:41:00

Solution 1

Example 1

insert into table1(approvaldate)values('20120618 10:34:09 AM');

Example 2

insert table1 (approvaldate)
      values (convert(datetime,'18-06-12 10:34:09 PM',5))

Posted 16-Mar-17 20:41pm

DELPIN SUSAI RAJ

Comments

CHill60 17-Mar-17 10:06am

Funny looking C# code