inserting date AND time into oracle

[jgroove]

could someone please tell me how to insert the date AND time into oracle. I have tried MANY different ways from examples I have found and NOTHING seems to work. I can't even find a reference to what Oracle wants as a parmameter. Here is what I have at this moment.

<cfset dt=DateFormat(now(),&quot;dd-mmm-yy&quot & &quot; &quot; & TimeFormat(now(),&quot;HH:mm:ss&quot>
<!---
<cfset dt=CreateODBCDateTime(now())>
it doesn't like this either
--->
<cfoutput>#dt#</cfoutput>


<cfquery username=&quot;tlc&quot; password=&quot;desmond&quot; datasource=&quot;coldfusion on slpr&quot; debug>
insert into signon
(datetime)
values
('#dt#')
</cfquery>


any enlightenment would greatly be appreciated
jgroove

 

[Glowball]

Dates and times in Oracle are so much fun! Just kidding...

The time is probably in there, it's just getting out that is difficult. If you are inserting

Now()

in ColdFusion or

SYSDATE

in Oracle, it has the time in there. To get it out, try this:

<cfquery name=&quot;get&quot; datasource=&quot;myDSN&quot;>
SELECT TO_CHAR(theDate,'MM-DD-YYYY HH:MI:SS AM') &quot;formattedDate&quot; FROM tablename
</cfquery>
<cfoutput query=&quot;get&quot;>
#get.formattedDate#<br>
</cfoutput>

To enter the date it might be easiest to use Oracle:

INSERT INTO tablename 
    (columnname)
VALUES
    (SYSDATE)

Hope that works for you!
You have to use

TO_CHAR()

to get the time out of that field.

 

[jgroove]

Glowball,

Thanks for your information. I think I have masterd the whole date/time thing from your help, except for this. I have a table like this:

signon
---------------
userid varchar2(50)
datetime date

I am trying to find the total number of records that occur for each hour of the day. I have a query that looks like this:

<cfloop index=&quot;hour&quot; from=&quot;8&quot; to=&quot;19&quot;>
<cfquery name=&quot;qtime&quot;>
select * from signon where
datetime > to_date('#hour#:00','HH24:MI') 
and datetime < to_date('#hour#:59','HH24:MI')
</cfquery>
<cfoutput>#hour#-#hour#:59=#qtime.recordcount#</cfoutput><br>
</cfloop>

with output that looks like this:

8-8:59=0
9-9:59=16
10-10:59=24
11-11:59=23
12-12:59=28
13-13:59=16
14-14:59=11
15-15:59=10
16-16:59=14
17-17:59=25
18-18:59=3
19-19:59=0

there are over 12,000 records in the table and I know that there are more than 16 records between 9:00-9:59 because I printed out all 12,000 records (by mistake,oops).

jgroove

 

[mikeshort]

Try this instead:

select * from signon where
 to_char( datetime , 'hh24') = #hour#

 

[Glowball]

Try this:

<cfquery name=&quot;qtime&quot;>
SELECT 	some_column, TO_CHAR(date_column,'HH24') &quot;theHour&quot;  
FROM 	signon 
ORDER BY TO_CHAR(date_column,'HH24')
</cfquery>
<cfoutput query=&quot;qtime&quot; group=&quot;theHour&quot;>
#qtime.theHour#:00 has #qtime.some_column# entries<br>
</cfoutput>