how to get the biggest date 1

[feshangi]

Let say I have following date outputs:

Date1 = NULL
Date2 = 12/12/04
Date3 = 12/19/03
Date4 = NULL
Date5 = 01/01/05

How can I get the biggest date which is 01/01/05?

I played with DateCompare() and I couldn't get this to work with multiple dates (specially if a date field is NULL).

Please advice.

Thanks,

Mike

 

[FALCONSEYE]

if you are getting the date values from a db , you can use

select max(somethingDate) ...
from..

hope it helps

 

[feshangi]

Thanks FALCONSEYE for the reply.

Each of these dates are from a different column and don't know if max(field1, field2, field3, ....) would work!

Am I correct?

Thanks,

Mike

 

[feshangi]

I got this far and now my list doesn't have any empty NULLs.

		<cfset myList = "">
		
		<CFIF IsDate(Date303)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date303, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date303, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date304)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date304, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date304, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date305)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date305, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date305, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date306)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date306, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date306, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date503)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date503, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date503, "MM/DD/YY")>
			</cfif>		
		</CFIF>

now how can i get the largest one from this list (myList)?

 

[r937]

Each of these dates are from a different column and don't know if max(field1, field2, field3, ....) would work!no, but there may be an alternative

what database is this please?

we should have a sticky in this forum that reminds people who post database-related coldfusion questions (as opposed to non-database-related coldfusion questions) that it would be a good idea to mention which database engine you're using, because the SQL is different for each one


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

it's SQL 2000

 

[r937]

okay, never mind, as far as i know SQL2000 does not support the sql standard GREATEST() function

it's like MAX() except that it doesn't operate on a column, it operates on a row (list of scalar values, actually)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

Is there any function to find the greatest date on a list?

 

[r937]

i suppose you could use ListSort and then ListGetAt for the first item...

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

ListSort won't work with Date!

 

[r937]

well, i guess it depends on how you set up your list of dates

i've just tested it on dates that i pulled from a database, and it worked just fine

i merely specified "text" as the sort type

<cfset dlist 
  = '#somedatesquery.d1#
    ,#somedatesquery.d2#
    ,#somedatesquery.d3#'>
<cfset dsort=ListSort(dlist,"text","desc")>
<cfset biggest=ListGetAt(dsort,1)>
<cfoutput>
<p>biggest=#biggest#</p>
</cfoutput>

worked fine for me

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

this is not smart enough to sort by year!

 

[feshangi]

I think I got it to work:

		<cfset myList = "">
		<CFIF IsDate(Date303)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date303, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date303, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date304)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date304, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date304, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date305)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date305, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date305, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date306)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date306, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date306, "MM/DD/YY")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date503)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date503, "MM/DD/YY")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date503, "MM/DD/YY")>
			</cfif>		
		</CFIF>

 

[feshangi]

and here is the rest of it:

		<cfset myList = ListSort(Replace(myList,"/", "", "ALL"),"numeric","desc")>
		<CFSET myList = ListGetAt(myList,1)>
 		<CFSET myList = Left(myList, 3) & "/" & Mid(myList,4,2) & "/" & Right(myList,2)>

 

[r937]

it goes without saying that you can't expect ListSort -- or any other type of sort -- to work correctly on dates that aren't in year-month-day sequence!!!

i mean, if you're going to go to the trouble of using DateFormat, why wouldn't you format them to "yyyy-mm-dd"?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

I like your idea a lot and I am going to change my codes

 

[feshangi]

Here is my final code and it's working perfectly:

		<cfset myList = "">
		<CFIF IsDate(Date303)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date303, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date303, "YYYYMMDD")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date304)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date304, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date304, "YYYYMMDD")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date305)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date305, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date305, "YYYYMMDD")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date306)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date306, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date306, "YYYYMMDD")>
			</cfif>		
		</CFIF> 
		<CFIF IsDate(Date403)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date403, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date403, "YYYYMMDD")>
			</cfif>		
		</CFIF>
		<CFIF IsDate(Date503)>
			<cfif listLen(myList) is 0>
			   <cfset myList = DateFormat(Date503, "YYYYMMDD")>
			<cfelse>
			   <cfset myList = myList & ", " & DateFormat(Date503, "YYYYMMDD")>
			</cfif>		
		</CFIF>
		
		<cfset myList = ListSort(myList,"numeric","desc")>
		<CFSET myList = ListGetAt(myList,1)>
 		<CFSET myList = Left(myList, 5) & "/" & Mid(myList,6,2) & "/" & Right(myList,2)>
		<cfset myList = DateFormat(myList, "MM/DD/YY")>