Datetime problems between c# and sql

Posted on 2006-03-24
Last Modified: 2010-04-16

I'm currently working on an SQL DB where one of the fields(Start_Time) in my shift table is a datetime datatype.

When I bind the values in my Start_Time field to a combobox in my front end it retrieves the values in the following format :   2006/03/22 03:40:21 PM

However if I run a normal select * query on my Shift Table  the same value as above is returned in the following format :  2006-03-22 15:40:21.000

What I'm trying to do is to use the selectd value in my combobox as a parameter in my storedprocedures but I am getting the [ 2006/03/22 03:40:21 PM]
value and not the [2006-03-22 15:40:21.000 ] value that my DB requires ! Please help!!
It`s driving me nuts!!
Question by:ChardiP
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    see here for how to use parameterized queries (including datetime) which is the best way:

    Author Comment

    Hi Thanks for the response but my problem is not the stored procedure .
    The problem is that the field returns two different types of formats for my datetime field
    LVL 10

    Accepted Solution

    when you write your date in the combobox it becomes a DateTime,
    you can just set the format of the DateTime

    here is the msdn example on formats for DateTime:

    DateTime july28 = new DateTime(1979, 7, 28, 5, 23, 15, 16);

    string[] july28Formats = july28.GetDateTimeFormats();

    // Print out july28 in all DateTime formats using the
    // default culture.
    foreach (string format in july28Formats) {

    IFormatProvider culture =
        new System.Globalization.CultureInfo("fr-FR", true);
    // Get the short date formats using the "fr-FR" culture.
    string [] frenchJuly28Formats =
                july28.GetDateTimeFormats('d', culture);

    // Print out july28 in various formats using "fr-FR" culture.
    System.Console.WriteLine("Starting fr-FR formats");
    foreach (string format in frenchJuly28Formats) {

    LVL 12

    Assisted Solution

    You can also use DateTime.ParseExact to parse your datetime.

    The thing is that when you run the select query on your table, you are only seeing the string representation of the value. As dkloeck says, you need to take it from a string value into a datetime, and pass a datetime into your stored procedure to ensure that you get the correct comparison.

    LVL 35

    Assisted Solution

    If your C# is sending the data to the stored procedure using the DateTime parameter and then the stored procedure is accepting it as a DateTime, then it should convert between the two.  I have not had problems with this.

    However, you could always format your date into the parts as shown by dkloeck or into a string and then do a cast and convert in SQL to ensure consistent formatting.
    LVL 35

    Expert Comment

    I think a split between these three comments:
    dkloeck & AGBrown & mrichmon

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now