如何从卡号区别选择日期值的最大值 [英] how to select Maximum value of date value from Distinct by Card No

问题描述

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

推荐答案

那我总是会成为一个问题。如果将日期存储为字符串，则总是会遇到麻烦，因为两个字符串的比较不是按日期顺序排列 - 它始终按字符串顺序排列，并逐字符地比较这两个值。显示差异的第一个字符决定哪个是最大的 - 而不是作为日期的值。



如果可以，那么您应该更改数据库以使用真正的DateTime列而不是字符串 - 此时此问题和许多潜在的未来消失。



如果你不能那么你需要将你的字符串转换为日期在SELECT语句中使用它之前的值 - 由于日期字符串甚至不是yyyy-MM-dd的正常SQL日期格式，因此没有很好的方法。

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.

这篇关于如何从卡号区别选择日期值的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！