Correct DateTime format in SQL Server CE?

Question

I have a C# DateTime class and wanted to know how I need to format it in a SQL Server CE query to insert it into the database, I was hoping to have both the date and time inserted in. Currently when I try variations thereof I get invalid format exceptions.

Current format I'm using is: dd/MM/yyyy, was hoping to do something like dd/MM/yyyy hh:mm:ss.

The way I'm trying to do the insert is like so:

 ( ( DateTime )_Value ).ToString( "dd/MM/yyyy hh:mm:ss" )

Obviously hh:mm:ss isn't working, if that isn't there dd/MM/yyyy executes successfully in the query.

I've tried a few formats including what I've found on google but none have worked so far...

Can you include your code so we can see what you have tried? — Jake1164
– Jake1164, Commented Aug 20, 2012 at 2:14
Have you been able to insert other values into the database? getting any exceptions / errors? — Jake1164
– Jake1164, Commented Aug 20, 2012 at 2:15
What is the field type you are inserting into? There is a difference between Date and DateTime in SQL Server. — Jake1164
– Jake1164, Commented Aug 20, 2012 at 2:18
Hi @Jake1164, yes I've been able to add other values in to he database without issues, it's just when I try to add time things go wrong... — meds
– meds, Commented Aug 20, 2012 at 2:18
Try year-month-day with a '-' between them. time formatted the same way yyyy-MM-dd hh:mm:ss — Jake1164
– Jake1164, Commented Aug 20, 2012 at 2:23

Joel Coehoorn · Accepted Answer · 2020-08-19 22:44:29Z

18

If you're worried about getting the format right at all, something has already gone seriously wrong. There are two things you need to do to correctly work with datetime values in any database, not just sqlce:

Make sure you're using a datetime type for the column (not a text type like varchar)
Make sure you're using a datetime parameter in a parameterized query, and not string concatenation.

If you do that, there is no formatting involved on your part. At all. Example:

 void SetDate(int recordID, DateTime timeStamp)
 {
    string SQL = "UPDATE [sometable] SET someDateTimeColumn= @NewTime WHERE ID= @ID";

    using (var cn = new SqlCeConnection("connection string here"))
    using (var cmd = new SqlCeCommand(SQL, cn))
    {
        cmd.Parameters.Add("@NewTime", SqlDbType.DateTime).Value = timeStamp;
        cmd.Parameters.Add("@ID", SqlDbType.Integer).Value = recordID;

        cn.Open();
        cmd.ExecuteNonQuery();
    }
}

Never ever ever ever EVER use string manipulation to substitute values into sql queries. It's a huge no-no.

edited Aug 19, 2020 at 22:44

answered Aug 20, 2012 at 2:42

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

4 Comments

Jake1164 Over a year ago

+1 For pointing out to NEVER use string manipulation to store dates.

mal Over a year ago

+1 for lesson in NEVER using string manipulation in SQL queries.

mal Over a year ago

can you do this: "UPDATE \@SomeTable SET \@SomeColumn ... etc"

mal Over a year ago

Stack overflow wouldn't let me post the at symbols, hence the \

Jake1164 · Accepted Answer · 2012-08-20 02:25:26Z

6

Try the following format:

DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")

answered Aug 20, 2012 at 2:25

Jake1164

12.4k6 gold badges49 silver badges65 bronze badges

Comments

Uwe Keim · Accepted Answer · 2021-10-11 19:02:57Z

1

Man, you do not need to convert string to DateTime.

Use a instance of a new DateTime and pass the date as parameter. Like this:

using (var ctx = new DBPreparoEntities())
{
    var _client = from p in ctx.Client
                     select new Client
                     {
                         data = new DateTime(2016,08,17),
                         dateconf = null,
                         scod_cli = p.Rua,
                         valorini = 7214.62m,
                     };
    return client.ToList();
}

don't use:

... data = DateTime.Parse("2016/12/10") // or other type convertions.

edited Oct 11, 2021 at 19:02

Uwe Keim

40.9k61 gold badges193 silver badges309 bronze badges

answered Oct 27, 2016 at 20:46

Rafael

97412 silver badges23 bronze badges

Comments

Uwe Keim · Accepted Answer · 2021-10-11 19:01:59Z

0

private void button1_Click(object sender, EventArgs e)
{
    var cnn1 ="";//connection string 
    SqlCeConnection cnn = new SqlCeConnection(cnn1);   
    datetime dt4 = DateTime.Today.Date.ToString("yyyyMMdd").trim();//format 
    var qry ="insert into tbl_test(User_Id, DateOfJoin)values (11,'" + dt4 + "')";
   
    cmd = new SqlCeCommand(qry, cnn);
   
    try
    {
        dr = cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        string sr = ex.Message;
        throw;
    }
}

Above code worked for me.

edited Oct 11, 2021 at 19:01

Uwe Keim

40.9k61 gold badges193 silver badges309 bronze badges

answered Apr 27, 2020 at 15:53

brahmam chary

11 bronze badge

2 Comments

con Over a year ago

a verbal explanation is often helpful for others

brahmam chary Over a year ago

//simply_pass _datetime _component _by converting to formatted string

Uwe Keim · Accepted Answer · 2021-10-11 19:02:33Z

0

sorry this is in vb.net, but this is a method i use to convert from a CE date/time format:

Public Shared Function ConvertSqlDateTimeFormat(ByVal s As String) As String
    Dim theDate As New Text.StringBuilder
    Dim sTemp As String = ""
    Dim iIndex As Integer

    If s.Length > 8 Then
        'first we do the time
        iIndex = s.IndexOf(" ", System.StringComparison.OrdinalIgnoreCase)
        If iIndex > 0 Then
            sTemp = s.Substring(iIndex).Trim
            iIndex = sTemp.IndexOf(".", System.StringComparison.OrdinalIgnoreCase)
            If iIndex > 0 Then
                sTemp = sTemp.Substring(0, iIndex)
            End If
        End If

        'next the date
        theDate.Append(s.Substring(4, 2))
        theDate.Append("/")
        theDate.Append(s.Substring(6, 2))
        theDate.Append("/")
        theDate.Append(s.Substring(0, 4))
        theDate.Append(" ")
        theDate.Append(sTemp)
    End If
    Return theDate.ToString
End Function

edited Oct 11, 2021 at 19:02

Uwe Keim

40.9k61 gold badges193 silver badges309 bronze badges

answered Aug 20, 2012 at 2:23

Phil

1,9732 gold badges29 silver badges61 bronze badges

2 Comments

Joel Coehoorn Over a year ago

The idea that you already have a method like this waiting to call is just wrong. String manipulation for sql data values has no place in secure code.

Phil Over a year ago

Ok, where is the security hole then? Granted, i misunderstood the question and was focusing on translating date from the database to a datetime format, rather than the other way around, but if the data is already in the database, i'm not sure what the problem is? If a datetime.tryparse does not give the correct result, what other options do you have?

Collectives™ on Stack Overflow

Correct DateTime format in SQL Server CE?

5 Answers 5

4 Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related