locked
insert/select datetime RRS feed

  • Question

  •  

    Hello,

    What is the query for inserting a data and time value for sql ce?

    and how can i select the rows that are between 2 date values?

    have tried everything but cant make it work..

    thnx in advance.

    by the way i am using sql mobile 2005 and vs2005..

    Friday, April 4, 2008 12:30 PM

Answers

  • Yes - I've had this problem in SQL, SQL Ce and Access. If you're not using a US date format, you have to format the date when you input it into any query. I use a select function on dates in my app with a WHERE clause that looks like this:
    "Date >= '" + sd.ToShortDateString() + "' AND Date <= '" + ed.ToShortDateString() + "'";

    where 'sd' is a start date and 'ed' is the end date (DateTime values in C#). Same approach for INSERT query.
    Tuesday, April 8, 2008 1:03 AM

All replies

  • its just

    DateIn datetime

    TimeIn datetime

     

    cmdInvoice.Connection = conn

    cmdInvoice.CommandText = "Insert into Invoice(DateIV,TimeIN,Total) values(@DateIN,@TimeIN,@Total)"

    Dim paramInves As SqlCeParameter = Nothing

    paramInves = New SqlCeParameter("@InvoiceDate", customerid)

    cmdInvoice.Parameters.Add(paramInves)

    paramInves = New SqlCeParameter("@TimeIN", invoicedate)

    cmdInvoice.Parameters.Add(paramInves)

    paramInves = New SqlCeParameter("@Total", invoicetime)

    cmdInvoice.Parameters.Add(paramInves)

     

    DateIN -- > DateTime.Now.Date

    TimeIN --> DateTime.Now.ToShortTimeString

    Friday, April 4, 2008 12:49 PM
  • i had a similar problem where by i was passing a UK formated date into the SQL CE DB and it wanted a US formated date.


    Try formating it liek so.

    Format to US Date

                    Dim usdate As DateTime = DateTime.Now()
                    usdate.ToLongTimeString()
                    usdate = Format(usdate, "MM/dd/yyyy hh:mm:ss")


    INSERT:

    db.Update("INSERT INTO table (Blah, DateIn) VALUES(" & CurrentJob & ", '" & usdate & "')")

    Not 100% sure if this will help, but had quite a few people in my office encounter similar problems.
    Monday, April 7, 2008 11:08 AM
  • Yes - I've had this problem in SQL, SQL Ce and Access. If you're not using a US date format, you have to format the date when you input it into any query. I use a select function on dates in my app with a WHERE clause that looks like this:
    "Date >= '" + sd.ToShortDateString() + "' AND Date <= '" + ed.ToShortDateString() + "'";

    where 'sd' is a start date and 'ed' is the end date (DateTime values in C#). Same approach for INSERT query.
    Tuesday, April 8, 2008 1:03 AM