Add date to sqlserver in yyyy-mm-dd

Question

When i add the lastImportedDate(dd-mm-yyyy) with the following method to the sql server everything is fine. In the database the date is yyyy-mm-dd

But add the lastImportedDate(dd-mm-yyyy) with a different pc on the same server the day and month are switched. In the database the date is yyyy-dd-mm.

internal static void insertSelloutSales(string CustomerID, string type, DateTime lastImported, string periodStart, string periodEnd)
{
     // Create SQL connection #connection
     SqlConnection sqlConnection1 = new SqlConnection(Connection.connectionString());
     SqlCommand cmd = new SqlCommand();
     cmd.CommandType = CommandType.Text;

     string periodstartQuery = periodStart;
     string periodEndQuery = periodEnd;

     // Create query with values and execute query
     if (!periodStart.Equals("NULL"))
     {
         periodstartQuery = " '" + periodStart + "'";
     }

     if (!periodEnd.Equals("NULL"))
     {
         periodEndQuery = " '" + periodEnd + "'";
     }

     cmd.CommandText = "Insert into CarsSellout (CustomerID, type, lastImportedDate, PeriodStart, PeriodEnd) VALUES ('" + CustomerID + "', '" + type + "', '" + lastImported + "', " + periodstartQuery + ", " + periodEndQuery + ")";
     cmd.Connection = sqlConnection1;
     sqlConnection1.Open();
     cmd.ExecuteNonQuery();
     sqlConnection1.Close();
}

Note that the date settings on the pc's are both set as dd-mm-yyyy.

if you need more info please add a comment.!

What can be the problem in this case?

Parameters of your method periodStart and periodEnd are strings. Change to datetime. Also change types of columns in database from varchar to datetime. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Jul 8, 2015 at 9:22
a) your data should be stored in the database as a date field, not as text; b) please use parameterized SQL. It may well fix this problem, and it will also protect you from SQL injection attacks. Never, never, never build SQL like this. — Jon Skeet
– Jon Skeet, Commented Jul 8, 2015 at 9:22
Date formatting is culture sensitive. Try using a specific CultureInfo, like en-US or Invariant. Another option is to explicitly format your date with ToString("yyyyMMdd") or such. — SvenL
– SvenL, Commented Jul 8, 2015 at 9:22
@SvenL: There's no reason to use string formatting at all, IMO. — Jon Skeet
– Jon Skeet, Commented Jul 8, 2015 at 9:22

Community · Accepted Answer · 2020-07-29 18:28:43Z

7

Do not insert your DateTime values with their string representations. Add your DateTime values directly to your parameterized queries.

SQL Server keeps your DateTime values in a binary format. They didn't have any format or something. What you saw them as yyyy-MM-dd or dd-MM-yyyy are just their textual representations.

Generating different string representations of a DateTime instance for different servers usually because they use different culture settings. But since you didn't show any relevant code that generates your strings, we never know.

Speaking of, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Please read carefully;

Bad habits to kick : choosing the wrong data type

As a best practice, use using statement to dispose your connections and commands automatically instead of calling Close methods manually.

using(var con = new SqlConnection(conString))
using(var cmd = con.CrateCommand())
{
    // Define your CommandText with parameterized query.
    // Define your parameters and their values. Add them with Add method to your command
    // Open your connection
    // Execute your query
}

edited Jul 29, 2020 at 18:28

CommunityBot

11 silver badge

answered Jul 8, 2015 at 9:24

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JP..t Over a year ago

Thank you for the clear answer, i will keep it in mind.!

Soner Gönül Over a year ago

@JP..t Good to hear it.

Collectives™ on Stack Overflow

Add date to sqlserver in yyyy-mm-dd

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related