You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Sisilla <si...@gmail.com> on 2007/01/31 15:34:26 UTC
Date Arithmetic - Add 5-day DURATION to CURRENT_DATE
How do I insert a date value of 5 days after the current date to a column
called 'responseDate' in a table called 'quotationRequest'? Perhaps, it
would be similar to the following?-:
INSERT INTO quotationRequest (responseDate) VALUES (DATE(current_date + 5
DAYS))
I appreciate any effort to help me. Thank you for your time and
consideration.
Yours,
Sisilla
--
View this message in context: http://www.nabble.com/Date-Arithmetic---Add-5-day-DURATION-to-CURRENT_DATE-tf3149013.html#a8729840
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Date Arithmetic - Add 5-day DURATION to CURRENT_DATE
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Anders Morken wrote (2007-01-31 15:49:46):
> Sisilla:
> > How do I insert a date value of 5 days after the current date to a column
> > called 'responseDate' in a table called 'quotationRequest'? Perhaps, it
> > would be similar to the following?-:
> >
> > INSERT INTO quotationRequest (responseDate) VALUES (DATE(current_date + 5
> > DAYS))
> >
> > I appreciate any effort to help me. Thank you for your time and
> > consideration.
You may use timestampadd which is defined as an escape syntax in the
JDBC spec. Example:
ij> values cast({fn timestampadd(SQL_TSI_DAY, 5, current_timestamp)} as date);
1
----------
2007-02-05
See the Derby reference manual for more details.
>
> I don't think Derby provides this in SQL - but Java provides this in the
> API:
>
> ps = connection.prepareStatement("INSERT INTO quotationRequest (responseDate) VALUES (?)");
>
> Date responseDate = (new GregorianCalendar()).add(Calendar.DAY_OF_MONTH, 5).getDate();
>
> ps.setDate(1, responseDate);
> rowsChanged = ps.executeUpdate();
>
> Kinda hackish, might not be correct or do exactly what you want, but you
> get the idea. =)
>
> (Note that the calendar classes in java.util are known for being...
> somewhat eccentric. Cavet emptor. =)
>
> --
> Anders Morken
>
> My opinions may have changed, but not the fact that I am right!
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Date Arithmetic - Add 5-day DURATION to CURRENT_DATE
Posted by Manjula G Kutty <ma...@gmail.com>.
Anders Morken wrote:
>Sisilla:
>
>
>>How do I insert a date value of 5 days after the current date to a column
>>called 'responseDate' in a table called 'quotationRequest'? Perhaps, it
>>would be similar to the following?-:
>>
>>INSERT INTO quotationRequest (responseDate) VALUES (DATE(current_date + 5
>>DAYS))
>>
>>I appreciate any effort to help me. Thank you for your time and
>>consideration.
>>
>>
>
>I don't think Derby provides this in SQL - but Java provides this in the
>API:
>
>ps = connection.prepareStatement("INSERT INTO quotationRequest (responseDate) VALUES (?)");
>
>Date responseDate = (new GregorianCalendar()).add(Calendar.DAY_OF_MONTH, 5).getDate();
>
>ps.setDate(1, responseDate);
>rowsChanged = ps.executeUpdate();
>
>Kinda hackish, might not be correct or do exactly what you want, but you
>get the idea. =)
>
>(Note that the calendar classes in java.util are known for being...
>somewhat eccentric. Cavet emptor. =)
>
>
>
Hi Sisilla,
This may be useful to you :
To return a timestamp value one month later than the current timestamp,
use the following syntax:
*{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}*
So to return a timestamp value 5 days later than the current timestamp
the syntax is
*{fn TIMESTAMPADD( SQL_TSI_DAY, 5, CURRENT_TIMESTAMP)}*
So in your case it should be
values ({fn TIMESTAMPADD( SQL_TSI_DAY, 5, CURRENT_TIMESTAMP)});
For more reference
http://db.apache.org/derby/docs/dev/ref/rrefjdbc88908.html
Thanks
Manjula
Re: Date Arithmetic - Add 5-day DURATION to CURRENT_DATE
Posted by Anders Morken <an...@stud.ntnu.no>.
Sisilla:
> How do I insert a date value of 5 days after the current date to a column
> called 'responseDate' in a table called 'quotationRequest'? Perhaps, it
> would be similar to the following?-:
>
> INSERT INTO quotationRequest (responseDate) VALUES (DATE(current_date + 5
> DAYS))
>
> I appreciate any effort to help me. Thank you for your time and
> consideration.
I don't think Derby provides this in SQL - but Java provides this in the
API:
ps = connection.prepareStatement("INSERT INTO quotationRequest (responseDate) VALUES (?)");
Date responseDate = (new GregorianCalendar()).add(Calendar.DAY_OF_MONTH, 5).getDate();
ps.setDate(1, responseDate);
rowsChanged = ps.executeUpdate();
Kinda hackish, might not be correct or do exactly what you want, but you
get the idea. =)
(Note that the calendar classes in java.util are known for being...
somewhat eccentric. Cavet emptor. =)
--
Anders Morken
My opinions may have changed, but not the fact that I am right!