?
Solved

Datetime problems between c# and sql

Posted on 2006-03-24
10
Medium Priority
?
439 Views
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!!
0
Comment
Question by:ChardiP
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16279393
see here for how to use parameterized queries (including datetime) which is the best way:
http://www.codeproject.com/cs/database/SqlSprocUtil.asp
0
 

Author Comment

by:ChardiP
ID: 16279475
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
0
 
LVL 10

Accepted Solution

by:
dkloeck earned 672 total points
ID: 16279496
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) {
    System.Console.WriteLine(format);
}

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) {
    System.Console.WriteLine(format);
}

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Assisted Solution

by:AGBrown
AGBrown earned 664 total points
ID: 16281602
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.

Andy
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 664 total points
ID: 16281767
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16566221
I think a split between these three comments:
dkloeck & AGBrown & mrichmon
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

850 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