how to select Maximum value of date value from Distinct by Card No

Question

0.00/5 (No votes)

See more:

CSS

ProcessDate ProcessTime CardNo  EffectiveDate   EffectiveTime   ExpireDate  ExpireTime
12/21/2011  10:04:14    0749    12/21/2011       00:00
12/21/2011  10:06:04    0749    12/21/2011       00:00
12/21/2011  23:17:37    0095    12/21/2011       00:00
12/29/2011  16:26:57    0095    12/29/2011       00:00
07/09/2013  13:00:14    0749    07/09/2013       00:00
07/09/2013  14:33:24    0749    07/09/2013       00:00
07/10/2013  12:42:58    0749    07/10/2013       12:30
08/01/2013  11:39:59    0019    01/08/2013       11:30
12/21/2011  10:30:20    0749                                       12/21/2011  10:30
01/31/2012  00:00:38    0095                                       01/31/2012  00:00
07/09/2013  14:33:26    0749                                       07/09/2013  14:30
07/10/2013  12:30:03    0749                                       07/10/2013  12:30
07/10/2013  13:23:15    0749                                       07/10/2013  13:00
07/10/2013  14:28:59    0749                                       07/10/2013  14:00
08/11/2013  14:59:23    0749                                       07/10/2013  14:30

I need to select the maximum value of EffectiveDate (MM/dd/yyyy).but that EffectiveDate  is taken in string value and also search by CardNo also. that code also write in below is there.

 i Want Result would be:

ProcessDate ProcessTime CardNo  EffectiveDate   EffectiveTime   ExpireDate ExpireTime

07/10/2013  12:42:58    0749    07/10/2013  12:30

12/29/2011  16:26:57    0095    12/29/2011       00:00

08/01/2013  11:39:59    0019    01/08/2013       11:30


but i tried one code.

var AutoEffectExpireData = " SELECT * FROM (SELECT AutoEffectExpireData.CardNo,Max(AutoEffectExpireData.EffectiveDate) AS EffectiveDate FROM AutoEffectExpireData GROUP BY AutoEffectExpireData.CardNo ) AS CardNo INNER JOIN AutoEffectExpireData t1 ON CardNo.CardNo = t1.CardNo and CardNo.EffectiveDate = t1.EffectiveDate ";


but output is came like this.

ProcessDate ProcessTime CardNo  EffectiveDate   EffectiveTime   ExpireDate  ExpireTime

12/21/2011  10:04:14    0749    12/21/2011       00:00

12/29/2011  16:26:57    0095    12/29/2011       00:00

08/01/2013  11:39:59    0019    01/08/2013       11:30

Posted 5-Oct-13 1:36am

Sharathkumar k.r

Updated 5-Oct-13 1:38am

v2

Add a Solution

Comments

Bernhard Hiller 7-Oct-13 3:28am

Why is that a "C# 3.5" question? That's SQL, not C#!

1 solution

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)

OriginalGriff · Answer 1 · 2013-10-05T03:29:00

That is always going to be a problem. If you store dates as strings, you will always get hassles, because the comparison for two strings is not in date order - it is always in string order, and compares the two values character by character. The first character that shows a diferdifference decides which is largest - not the value as a date.

If you can, then you should change your database to use genuine DateTime columns, rather than string - at which point this problem and a lot of potential future ones disappear.

If you can't then you need to convert your string to a date value before you use it in your SELECT statement - and there is no nice way to do that as your date string isn't even in normal SQL date format of yyyy-MM-dd.