Comparing .Net Date with Date Stored in SQL

Question

0.00/5 (No votes)

See more:

I am passing a date from C# and comparing the same with a date field from the database to retrieve a record but not able to do so since the date format is not matching.
The date coming from C# is like '11/24/2011 10:59:50 AM' while the date in the database is '2011-11-24 10:59:50.360'
How do I convert the C# date to the format of the Database?

Posted 24-Nov-11 18:53pm

gpthakur

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

deepakdynamite · Answer 1 · 2011-11-24T18:55:00

Solution 1

C#

DateTime a = GetFirstDate();
DateTime b = GetSecondDate(); // date from SQL

if (a.Date.Equals(b.Date))
{
    // the dates are equal
}

Posted 24-Nov-11 18:55pm

deepakdynamite

Updated 24-Nov-11 19:01pm

v2

Comments

RaviRanjanKr 25-Nov-11 9:04am

My 5+

Eduard Lu · Answer 2 · 2011-11-24T19:03:00

Solution 2

Covert date from c# to this format:

string date = DateTime.Now.ToString("mm-dd-yyyy hh:MM:ss.fff");

Then compare.

Regards.

Posted 24-Nov-11 19:03pm

Eduard Lu

Comments

gpthakur 25-Nov-11 3:56am

I am able to convert the datetime but I am missing milliseconds in the format due to which I am not able to match the records in the DB.
What I am getting is 2011-11-24 10:59:50:000' but what I need is 2011-11-24 10:59:50:360

[no name] 25-Nov-11 4:08am

i'll add a new solution, check it out. im pretty sure this one will work.

rajeevcapgeminiindia · Answer 3 · 2011-11-24T19:03:00

Solution 3

You can change the format of the C# Date as below

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

This gives you the similar output as DataBase Date format is.

Posted 24-Nov-11 19:03pm

rajeevcapgeminiindia

Comments

gpthakur 25-Nov-11 1:30am

Hi Rajeev,

I have a field as LastUpdate which I am appending to my query as below. How do I then convert it?

Select UA.Reaction, UAD.OnsetDate, LastEpisodeDate, TreatmentMethod,ClinicianName,UA.Comments from UserAllergy UA Inner Join UserAllergyDetail UAD on UA.LastUpdate = UAD.LastUpdate where UA.LastUpdate ='" + LastUpdate + "'"

Eduard Lu · Answer 4 · 2011-11-24T22:09:00

Solution 5

Hi again gpthakur,

Hope this one works! try this one instead:

C#

string date = DateTime.Now.ToString("mm-dd-yyyy");
string time = DateTime.Now.ToString("hh:MM:ss.fff");

string result = date + " " + time;

Please mark this solution as solved it fixes your problem.

Best Regards,
Eduard

Posted 24-Nov-11 22:09pm

Eduard Lu

Comments

gpthakur 25-Nov-11 4:37am

Eduard, this is not working :(
Have a look at the below code.

Select UA.Reaction, UAD.OnsetDate, LastEpisodeDate, TreatmentMethod,ClinicianName,UA.Comments from UserAllergy UA Inner Join UserAllergyDetail
UAD on UA.LastUpdate = UAD.LastUpdate where UA.LastUpdate ='" + LastUpdate.ToString(dtformat)

This is my query through which I am reading the field LastUpdate.
How do I format this field to get the desired output is my problem.
Currently what I am using is LastUpdate.ToString(dtformat) where dtformat is "yyyy-MM-dd HH:mm:ss.fff"

[no name] 25-Nov-11 4:45am

Correct me if im wrong:
OUPUTS:
C# = '2011-11-24 10:59:50 AM'
SQL = '2011-11-24 10:59:50.360'

are these the values you are getting currently?
there's no need to convert the data you get from sql. just convert the date in c# as to what the sql date format is.

gpthakur 25-Nov-11 5:10am

Yes, what you values u have mentioned here is correct and I am not converting SQL values. But when I am trying to convert the C# format to SQL, I am losing the milliseconds and hence I am not getting a perfect match for database records. The problem in on line where I am specifying the format for date time i.e yyyy-MM-dd HH:mm:ss.fff

[no name] 25-Nov-11 5:20am

i cant see the problem. Im getting the correct format using the above code.

C# = '2011-11-24 10:59:50.360'

gpthakur 25-Nov-11 5:25am

Eduard, when I run through the SQL Profile, I get the below query which trims the milliseconds

Select UA.Reaction, UAD.OnsetDate, LastEpisodeDate, TreatmentMethod,ClinicianName,UA.Comments from UserAllergy UA Inner Join UserAllergyDetail
UAD on UA.LastUpdate = UAD.LastUpdate where UA.LastUpdate ='2011-11-24 10:59:50.000'
Tks for your help anyways :)

[no name] 25-Nov-11 5:27am

That's the reason why, maybe you just follow the format of the sql result :D

[no name] 25-Nov-11 5:28am

np :)

D K N T H · Answer 5 · 2011-11-24T21:40:00

Solution 4

set c# date with this format ,

string date = DateTime.Now.ToString("mm-dd-yyyy hh:MM:ss.fff");

then compare..

hope it helps!

Posted 24-Nov-11 21:40pm

D K N T H

Comments

[no name] 25-Nov-11 3:43am

Hey you, you stole my comment!