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 derfloh 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

Status
Not open for further replies.

help100

Programmer
Joined
Aug 1, 2000
Messages
2
Location
CA
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>&lt;CFQUERY NAME=&quot;Insert1&quot; DATASOURCE=#source#&gt;<br>INSERT into test<br>VALUES ('#enterdate#' +1,'#name#')<br>&lt;/cfquery&gt;<br>
 
It looks like you are using MS Access.&nbsp;&nbsp;&nbsp;If so, the DateAdd() function should do the trick.&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Description<br>interval&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Required. String expression that is the <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;interval of time you want to add.<br>number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Required. Numeric expression that is the<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;number of intervals you want to add. It can <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;be positive (to get dates in the future) or <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative (to get dates in the past).<br>date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Required. Variant (Date) or literal <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;representing date to which the interval is <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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 (&quot;y&quot;), Day (&quot;d&quot;), or Weekday (&quot;w&quot;).<br>The DateAdd function won't return an invalid date. The following example adds one month to January 31:<br><br>DateAdd(&quot;m&quot;, 1, &quot;31-Jan-95&quot;)<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>
 
HI<br>&nbsp;I am using oracle and not MS access.does dateadd work also for oracle?as it is not working properly and says missing comma.<br>thanks<br>
 
Just doing normal addition should work in Oracle.&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;I would think that that problem is in your VALUES clause.&nbsp;&nbsp;&nbsp;Are you providing values for each field in the new record?<br><br>I also don't recognize Oracle using the &quot;#&quot; signs.&nbsp;&nbsp;&nbsp;That is what made me think you were using MS Access.&nbsp;&nbsp;&nbsp;If you are trying to prompt for those values, I believe you need to use the &quot;&&quot; and/or &quot;&&&quot;, but I am not sure.&nbsp;&nbsp;&nbsp;<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
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top