Quantcast

Simple Date Function

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Simple Date Function

Chris Forbis
Sorry to bug you all with this, but I know I have to be missing something very simple :)
 
I need to do this:
 
update AGENT set state='Test', substate='',
timestamp=TO_DATE('2006-09-01 22:22:22','yyyy-mm-dd hh24:mi:ss')
where aid=1
 
 
But I get a SQL Exception telling me TO_DATE is not a valid token.. :)  so how do I do this?
 
 
Reason for this?  because setObject(myDateObject) on a preparedStatement is removing the time part of my date...
 
Thanks!
 
Chris

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Simple Date Function

Fred Toussi-2
update AGENT set state='Test', substate='2006-09-01 22:22:22' where aid=1
 
You should bear in mind that DATE type does not include hours, etc. You need to define your table column SUBSTATE as TIMESTAMP
 
Fred

----- Original Message -----
Sent: 01 September 2006 19:41
Subject: [Hsqldb-user] Simple Date Function

Sorry to bug you all with this, but I know I have to be missing something very simple :)
 
I need to do this:
 
update AGENT set state='Test', substate='',
timestamp=TO_DATE('2006-09-01 22:22:22','yyyy-mm-dd hh24:mi:ss')
where aid=1
 
 
But I get a SQL Exception telling me TO_DATE is not a valid token.. :)  so how do I do this?
 
 
Reason for this?  because setObject(myDateObject) on a preparedStatement is removing the time part of my date...
 
Thanks!
 
Chris


-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642


_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Simple Date Function

keithaniz
 I 'm using this script to create table tx_in_transit ..
-------------------------------  
CREATE TABLE RTN_TEST.TX_IN_TRANSIT ("ID" BIGINT,
"PATIENT_NUM" VARCHAR(100),
"CLAIM_NUM" VARCHAR(100),
"CLAIM_SEQ_NUM" VARCHAR(40),
"FIRING_CONTEXT_NUM" VARCHAR(40),
"PROVIDER_NUM" VARCHAR(100),
"TX_REQUEST_REF" BIGINT,
"CORRELATOR_ID" BIGINT,
"ROUTE" VARCHAR(500),
"INTERCHANGE" LONGVARBINARY,
"TT_START"  TIMESTAMP DEFAULT SYSDATE,
"TT_STOP" DATE DEFAULT TO_DATE( '30/11/9999', 'DD/MM/YYYY')
)
-------------------------------------------
and i got this error..
Caused by: org.hsqldb.HsqlException: unexpected token: TO_DATE : line: 12778
        at org.hsqldb.error.Error.parseError(Unknown Source)
        at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
        at org.hsqldb.ParserRoutine.readDefaultClause(Unknown Source)
        at org.hsqldb.ParserDDL.readColumnDefinitionOrNull(Unknown Source)
        at org.hsqldb.ParserDDL.compileCreateTableBody(Unknown Source)
        at org.hsqldb.ParserDDL.compileCreateTable(Unknown Source)
.....


Any suggestion Plzzzz!!!!!!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Simple Date Function

Fred Toussi-2
Use a DATE literal for the default.

"TT_STOP" DATE DEFAULT DATE '9999-11-30'

On Fri, May 24, 2013, at 10:24, risaldeep wrote:

>  I 'm using this script to create table tx_in_transit ..
> -------------------------------  
> CREATE TABLE RTN_TEST.TX_IN_TRANSIT ("ID" BIGINT,
> "PATIENT_NUM" VARCHAR(100),
> "CLAIM_NUM" VARCHAR(100),
> "CLAIM_SEQ_NUM" VARCHAR(40),
> "FIRING_CONTEXT_NUM" VARCHAR(40),
> "PROVIDER_NUM" VARCHAR(100),
> "TX_REQUEST_REF" BIGINT,
> "CORRELATOR_ID" BIGINT,
> "ROUTE" VARCHAR(500),
> "INTERCHANGE" LONGVARBINARY,
> "TT_START"  TIMESTAMP DEFAULT SYSDATE,
> "TT_STOP" DATE DEFAULT TO_DATE( '30/11/9999', 'DD/MM/YYYY')
> )
> -------------------------------------------
> and i got this error..
> Caused by: org.hsqldb.HsqlException: unexpected token: TO_DATE : line:
> 12778
> at org.hsqldb.error.Error.parseError(Unknown Source)
> at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
> at org.hsqldb.ParserRoutine.readDefaultClause(Unknown Source)
> at org.hsqldb.ParserDDL.readColumnDefinitionOrNull(Unknown Source)
> at org.hsqldb.ParserDDL.compileCreateTableBody(Unknown Source)
> at org.hsqldb.ParserDDL.compileCreateTable(Unknown Source)
> .....
>
>
> Any suggestion Plzzzz!!!!!!
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Simple-Date-Function-tp852p3891.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring
> service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt!
> http://p.sf.net/sfu/newrelic_d2d_may
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_may
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Simple Date Function

keithaniz
Thanks it works
but if i need custom date by default how i supposed to do
eg: '12/31/9999', of format 'MM/DD/YYYY'
Loading...