Adding days to a stored Date/Time

[Eric_Dahlenburg]

Hi,

Here is what I am trying to do.

I am trying to add an record field called #Warranty_Dump_Info.Reference# to a record Field
called #Warranty_Dump_Info.Purchase_Date# and get a future date that would represent the experation date of a factory warranty.

#Warranty_Dump_Info.Reference# is a numerical value (30, 90, 180, etc) representing the number of days in the warranty and #Warranty_Dump_Info.Purchase_Date# is date/time value (Example: 2009-01-01 00:00:00 )

I can get the date/time formatted (Time stripped out),
using <foutput>#dateformat(Warranty_Dump_Info.Purchase_Date, "mm-dd-yyy")#</output>

However, I cannot get it to add the number of days value from #Warranty_Dump_Info.Reference# or get it to display
in the correct date format (Time Stripped off).

I have tried stripping the time from #Warranty_Dump_Info.Purchase_Date# and then saving it as a variable, then
using DateAdd() on the variable, but it still fails to strip the time from the results.

Thank You in advance for your help.


Also, what does it mean when the date is displayed as follows: {ts '2009-01-01 00:00:00'}
Some of my CF attemps have resulted in this weird "ts" display. You can see it at the bottom of the page here: http://devo.dns2go.com/Warranty_Dump.cfm (logon as Admin, password: admin). This is where I have been trying to experiment and figure it out.

[-__cfSearching__-]

> However, I cannot get it to add the number of days value

Can you elaborate on how it is not working? Typically, you perform any date math first. Then use DateFormat to format the final result. For example:

<cfoutput query="yourQuery" >
Number of Days: #Reference#<br/>
Purchase_Date: #Purchase_Date#<br/>
<!--- calculate the expiration date --->
Expiration Date: #DateAdd("d", Reference,. Purchase_Date)#<br/>
<!--- format the expiration date as desired --->
Formatted: #DateFormat( DateAdd("d", Reference,. Purchase_Date), "mm-dd-yyyy")#<br/>
</cfoutput>

> Also, what does it mean when the date is displayed as follows: {ts '2009-01-01 00:00:00'}

It is a standard format (odbc/jdbc) used to represent date/time values. Since date values can be interpreted different ways (mm-dd-yyyy, dd-mm-yyyy, etcetera) the standard helps ensure that different systems will all interpret the date value in the same way.

[Eric_Dahlenburg]

Thank You so much for the reply. Here is the final code that works (Part of a table):

<td width="109" class="style1"><span class="style1">#DateFormat( DateAdd("d", Warranty_Dump_Info.Warranty_Days, Warranty_Dump_Info.Purchase_Date), "mm-dd-yyyy")#</span></td>

Works great (It was actually close to what I had tried earlier, but my syntax was off).

Here is the rest of the problem:

I need to change the query to only show results for items who's warranty expiration date is greater than or equal to the current date. Here is my query so far:

<cfquery name="Warranty_Results" datasource="Parts" username="admin" password="dallas">
SELECT *
FROM Warranty INNER JOIN Vendor_Table
ON Warranty.Vendor_Num = Vendor_Table.Vendor_Num
WHERE Warranty.Part_Number='Test1234'
AND #DateAdd("d", Warranty_Results.Warranty_Days, Warranty.Purchase_Date)# <= #Now()#
ORDER BY Part_Number, Serial_Number ASC
</cfquery>

I get the message that the Element Warranty_Days is undefined in Warranty_Results.
Looks like CF is not interpreting the feilds as feild names. Any Ideas?

Thanks,

Eric

[-__cfSearching__-]

> I need to change the query to only show results for items who's warranty expiration
> date is greater than or equal to the current date.

You will need to use your database's date functions. I do not know which one you are using, but most have an equivalent DateAdd function. Though the function name may differ.

> #DateAdd("d", Warranty_Results.Warranty_Days, Warranty.Purchase_Date)# <= #Now()#
> Looks like CF is not interpreting the feilds as feild names

No, CF does not communicate with your database the way your are thinking. It does not know anything about your database (tables, columns, etcetera). CF just converts the code inside your cfquery tags into a big sql string. Then it sends the string to your database for execution. Your database does not know anything ColdFusion and vice versa. That is why you must use your database's date functions. Make sense?

[Eric_Dahlenburg]

Thanks, Actually, it doesn't make sense...

I am using an Access DB, I can make a change( add field) and make every record have a field equal to the
future date at which the warranty expires9 Purchase_Date + Warranty Days). In the following example, that field is called Warranty.Purchase_Date. It contains dates that are newer and older than todays date. Check out the code for the query:

<cfquery name="Warranty_Results" datasource="Parts" username="admin" password="dallas">
SELECT *
FROM Warranty INNER JOIN Vendor_Table
ON Warranty.Vendor_Num = Vendor_Table.Vendor_Num
WHERE #dateformat(Warranty.Purchase_Date, "mm-dd-yyyy")# >= #dateformat(Now(), "mm-dd-yyyy")#
AND Warranty.Part_number = 'Test1234'
ORDER BY Part_Number, Serial_Number ASC
</cfquery>

I am making the query compare the formatted date of Warranty.Purchase_Date and the formatted date of the function, Now() and returning all records that are greater or equal to todays date.

However, I get the error message " Element Purchase_Date is undefined in Warranty". The Warrenty table definetly has a field called Purchase_Date, so what is wrong?

Thank You in Advance,

Eric

[-__cfSearching__-]

> WHERE #dateformat(Warranty.Purchase_Date, "mm-dd-yyyy")# >= #dateformat(Now(), "mm-dd-yyyy")#

When that code executes, CF is not even connected to your database. All ColdFusion code executes on the ColdFusion web server, _not_ in your database. So CF cannot possibly know anything about your database tables or how to manipulate them. (Nor does your database understand anything about ColdFusion code)

Since that code is running on the CF server, ColdFusion thinks "Warranty.Purchase_Date" is some sort of variable. Obviously there is no CF variable in your code with that name. That is why you get the error "Element ... is undefined" error.

To perform the date manipulations in the sql (ie database), you have to use your _database's_ date functions. IIRC, Access version of this function is almost identical:

Update:
<!--- psuedo code --->
WHERE dateAdd('d', numberOfDays, theDateColumn) <= Date()

[Eric_Dahlenburg · Accepted solution]

O.K., now I see what you are saying....

I've been using CF functions in the SQL...

Here is the working code for those following this thread:

<cfquery name="Warranty_Results" datasource="Parts" username="admin" password="dallas">
SELECT *
FROM Warranty INNER JOIN Vendor_Table
ON Warranty.Vendor_Num = Vendor_Table.Vendor_Num
WHERE dateadd('d', Warranty_Days, Purchase_Date)>=Date()
AND Warranty.Part_Number='#URLDecode (URL.PartNumber)#'
ORDER BY Part_Number, Serial_Number ASC
</cfquery>

Thank You for the help !

Eric