HI I need to incremnet a date value by one and insert it into the table.could u please help?i am using date +1 format but it gives an erorr.<br><br>please help.<br>the code is as follows<br><CFQUERY NAME="Insert1" DATASOURCE=#source#><br>INSERT into test<br>VALUES ('#enterdate#' +1,'#name#')<br></cfquery><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Incrementing date value
- Thread starter help100
- Start date
It looks like you are using MS Access. If so, the DateAdd() function should do the trick. According to MS Access Help:<br><br>Returns a Variant (Date) containing a date to which a specified time interval has been added.<br><br>Syntax<br><br>DateAdd(interval, number, date)<br><br>The DateAdd function syntax has these named arguments:<br><br>Part Description<br>interval Required. String expression that is the <br> interval of time you want to add.<br>number Required. Numeric expression that is the<br> number of intervals you want to add. It can <br> be positive (to get dates in the future) or <br> negative (to get dates in the past).<br>date Required. Variant (Date) or literal <br> representing date to which the interval is <br> added.<br><br>Settings<br><br>The interval argument has these settings:<br><br>Setting Description<br>yyyy Year<br>q Quarter<br>m Month<br>y Day of year<br>d Day<br>w Weekday<br>ww Week<br>h Hour<br>n Minute<br>s Second<br><br>Remarks<br><br>You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.<br>To add days to date, you can use Day of Year ("y"
, Day ("d", or Weekday ("w".<br>The DateAdd function won't return an invalid date. The following example adds one month to January 31:<br><br>DateAdd("m", 1, "31-Jan-95"<br><br>In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.<br><br>If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.<br><br>If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.<br><br>Hope it works!! <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>
, Day ("d", or Weekday ("w".<br>The DateAdd function won't return an invalid date. The following example adds one month to January 31:<br><br>DateAdd("m", 1, "31-Jan-95"<br><br>In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.<br><br>If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.<br><br>If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.<br><br>Hope it works!! <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>- Thread starter
- #3
Just doing normal addition should work in Oracle. I have several delete queries that I use to purge old data from a table that use the format:<br><br>DELETE FROM table1 <br>WHERE substr(date_field,1,10) like SYSDATE - 14;<br><br>You say the error message says you are missing a comma. I would think that that problem is in your VALUES clause. Are you providing values for each field in the new record?<br><br>I also don't recognize Oracle using the "#" signs. That is what made me think you were using MS Access. If you are trying to prompt for those values, I believe you need to use the "&" and/or "&&", but I am not sure. <br><br>Hope this helps... <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>
- Status
Similar threads
- Locked
- Question