House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Talk (CF-Talk)

DateAdd in Query Statement

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Mark Leder wrote:
Jim Wright
11/27/06 09:27 A
Mark Leder wrote:
Jim Wright
11/27/06 09:56 A
Try:
Ryan Stewart
11/27/06 09:20 A
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Mark Leder
11/27/2006 09:15 AM

  I'm attempting to filter a query set based on a date.  However, it looks like the DateAdd function is not in an ODBC format, so I can't compare the current date to the filePostDate + twgFileExpDate.  I've looked around CFLib without success.  How would I write this? <cfset ARGUMENTS.twgFileExpDate = 21> SELECT F.fileURL, F.filePostDate, F.caseID FROM #APPLICATION.prefix#_Clients_Files_List F WHERE F.caseID = <cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.caseID#">      AND #CreateODBCDateTime(Now())# <= #DateAdd('D',ARGUMENTS.twgFileExpDate,F.filePostDate)# Thanks, Mark

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jim Wright
11/27/2006 09:27 AM

Mark Leder wrote: ----- Excess quoted text cut - see Original Post for more ----- You are trying to do the dateadd on F.filePostDate on the CF side of things, but the F.filePostDate value will not exist until the query is being run...use a function on the db side of things... AND #CreateODBCDateTime(Now())# <= DATEADD(day, #ARGUMENTS.twgFileExpDate#, F.filePostDate)

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Mark Leder
11/27/2006 09:35 AM

Outstanding!  That was it (the filePostDate value not existing problem). Thanks Jim and Ryan. Thanks, Mark Mark Leder wrote: ----- Excess quoted text cut - see Original Post for more ----- You are trying to do the dateadd on F.filePostDate on the CF side of things, but the F.filePostDate value will not exist until the query is being run...use a function on the db side of things... AND #CreateODBCDateTime(Now())# <= DATEADD(day, #ARGUMENTS.twgFileExpDate#, F.filePostDate)

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jim Wright
11/27/2006 09:56 AM

Mark Leder wrote: > Outstanding!  That was it (the filePostDate value not existing problem). > Thanks Jim and Ryan. > You also might want to use the CreateODBCDate function (without the time)...with the time part, you will have cases where... if now() = 11/27/2006 09:46AM and F.filePostDate = 11/6/2006 00:00AM #CreateODBCDateTime(Now())# <= #DateAdd('D',21,F.filePostDate)# will be FALSE but... #CreateODBCDate(Now())# <= #DateAdd('D',21,F.filePostDate)# will be TRUE My guess is that you don't want to take the time into account...but then I'm not sure exactly what you are doing.

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jim Wright
11/27/2006 10:00 AM

sorry...shouldn't have had those #s around the right hand sides. fixed below. Jim Wright wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Ryan Stewart
11/27/2006 09:20 AM

Try: #CreateODBCDateTime(DateAdd('D',ARGUMENTS.twgFileExpDate,F.filePostDate))# Ryan >>> mel@markleder.com 27/11/2006 14:13 >>>   I'm attempting to filter a query set based on a date.  However, it looks like the DateAdd function is not in an ODBC format, so I can't compare the current date to the filePostDate + twgFileExpDate.  I've looked around CFLib without success.  How would I write this? <cfset ARGUMENTS.twgFileExpDate = 21> SELECT F.fileURL, F.filePostDate, F.caseID FROM #APPLICATION.prefix#_Clients_Files_List F WHERE F.caseID = <cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.caseID#">      AND #CreateODBCDateTime(Now())# <= #DateAdd('D',ARGUMENTS.twgFileExpDate,F.filePostDate)# Thanks, Mark


<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

December 22, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
   1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31       

Designer, Developer and mobile workflow conference