inserting date/time in to oracle table

Thank you jyokum.It is inserting something like 2003-08-25 15:04:05.0.What is the last ".0" mean?I just want it to insert
"2003-08-25 15:04:05"?Could you please expalin me?

Thanks
Meghu

i'm guessing it's milliseconds

you could always just format it the way you want after selecting it

Actually you can simply have it this way :

<cfset submitdate = DateFormat(Now(),"yyyy-mm-dd HH:mm:ss">
Insert into table1(name,ssn,datesubmitted,email)
Values('xxx','xxx-xxx-xxxx',#submitdate#,'test@hotmail.com')

Another option is that you can also set a default value for your datesubmitted field as current datetime. So you don't need to pass a value for that field.

Goodluck!
eNTRANCE2002 :-)

try using
<cfqueryparam value="#now()#" cfsqltype="cf_sql_date">

& it shld automatically insert "date time" in ur ORACLE table

thats what i do !

K'Rgds
Anand

Hi entrance2002,

iam getting the below error when i use your code.Could you please explain?

date format picture ends before converting entire input string

Thanks
Meghu

meghu - did u have a look at what i suggested

cfsqltype="cf_sql_date" - shld do it for u

Anand,
If i do that and try to pull the record again ,it just displays the date part only and gives the time part as 00:00:00.0.
But i want the current time also.Could you please explain?

Thanks
Meghu

use cfqueryparam, I assure you it will be easier.

the date format error is the result of the date you're passing in being in a different format than what's defined in NLS date format

that's why you have to use cf_sql_timestamp instead of cf_sql_date

Jyokum,

I works if i use cf_sql_timestamp as you said. But the problem is when i try to pull up the record again it displays wrong data.For example if datesbmitted(sys date in this case) is "08/26/2003 14:34:28" ,when i pull up the record it displays "08/26/2003 14:08:28" .That is it is displaying month in place of minutes.I did not do any date or time formatiing when  displaying.
Could you explain please?

Aslo in some other place of the program the user enters a date and time and i should update the record with the entered date time value.Oracle doesnot allow me to update even though i tried to format it before updating.

Thanks
Meghu

what does your select statement look like when you pull the date back out?

SELECT tochar(datesubmitted,'MM/DD/YYYY HH24:MI:SS') FROM table1


make sure your not using MM to represent minutes

Thank you jyokum.
Iam trying to update a record with the date user entered.Its giving me an error.Below is the code

<cfif isdate(form.txtdate)>
    <cfset tmpdate = dateFormat(#form.txtdate# , "dd-mmm-yyyy HH:MM:ss")>
</cfif>

update table1
set rdate = '#tmpdate#'
where sno = '10000';

Any help is appreciated.thanks

and even in the above case its taking month in place of minutes.and the error is "date format picture ends before converting entire input string"

say for ex the date entered is 09/20/2003 13:35:20

the query in debug looks like

update table1
set rdate='20-sep-2003 13:09:20'
where sno='10000'

and i get the above error

try this

update table1
set rdate = todate('#tmpdate#','MM/DD/YYYY HH24:MI:SS')
where sno = '10000';

it gives me "invalid column name' error

oops, I think it's supposed to be double quotes

update table1
set rdate = todate("#tmpdate#","MM/DD/YYYY HH24:MI:SS")
where sno = '10000';

still the same error even with double quotes.
I have a doubt, can we use todate to format a coldfusion varaible,because the right hand side of the expression is coldfusion.Please help.

Thanks

here's an insert statement I use that works just fine

<cfparam name="dtstamp" default="#dateformat(now(),'dd-mmm-yy')# #timeformat(now(),'HH:mm:ss')#">
<cfquery name="qryInsert" datasource="#request.dsn#">
INSERT INTO
    tblLOG
VALUES (
    to_date('#dtstamp#', 'DD-MON-RRRR HH24:MI:SS')
)
</cfquery>

thanks

is it working now?

I tired to use the <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp"> that was mentioned here and all it shows on my insert is (param 1) with the error ORA-01861: literal does not match format string and the date format that actually goes in if hard coded is 2003-08-25 15:04:05.0, the this should work, what could I be doing wrong?
<cfquery name="Site_Update" datasource="ManualEntry">
      INSERT INTO
            BY_SITE (SITE, MONTH_END_DT, DW_CREATE_DT, DW_UPDATE_DT, DM_PUSH_DT)
      VALUES
            ('CD', '2003-08-25 15:04:05.0','2003-08-25 15:04:05.0',
            <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">, '2003-08-25 15:04:05.0')
</cfquery>

Thanks a million for any help!
Va. :-)

HI.

In Mssql, I use
insert into blabal (fieldname) values (getdate())

Hope that helps.

-Ejay