if you don't specify a time when using to_date Oracle will use zeros
(midnight of previous day, or beginning of new day) for the timestamp. That
timestamp is part of the date so if you used Oracle's default sysDate when
inserting the record then the timestamps won't match. Try this:
to_char(tblDetailEntry.recorddate,'mm/dd/yyyy') = '#currentdate#'
instead of your current date comparison (it will strip off the timestamp).
Bryan Love Macromedia Certified Professional
Internet Application Developer / Database Analyst
Telecommunication Systems Inc.
[EMAIL PROTECTED]
"...'If there must be trouble, let it be in my day, that my child may have
peace;' and this single reflection, well applied, is sufficient to awaken
every man to duty"
- Thomas Paine, The American Crisis
-----Original Message-----
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 19, 2001 11:09 AM
To: CF-Talk
Subject: RE: SQL To Sum data
Dunno oracle well, but here's a couple of ideas:
First, are there nulls in the field you're taking the sum of? This may case
erratic results. Try SUM(ISNULL(tbldetailentry.reghrs),0) instead.
Also, be careful with date comparisons. They don't trim off the time
portion in sql server. Perhaps there's a time portion in the fields. Try
trimming of the time by casting the database field as float, taking the
floor, and casting back as date time. Or alternatively you can use
tblDetailEntry.recorddate >= '10/01/2001 00:00:00' and
tblDetailEntry.recorddate <= '10/01/2001 23:59:59'
The equals operator always takes time into account for these fields in sql
server. I suspect that it's doing the same in your Oracle database...
-----Original Message-----
From: Angel Stewart [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 19, 2001 10:55 AM
To: CF-Talk
Subject: SQL To Sum data
--------------------------
SELECT SUM(tbldetailentry.reghrs) AS Daystotals
FROM tblMainEntry,TblDetailEntry
WHERE
tbldetailentry.MainID = tblMainEntry.MainID
AND
tblDetailEntry.recorddate =
to_date('#currentdate#','mm/dd/yyyy')
AND
tblMainEntry.empid = #client.emplid#
AND
Attendance_Code > 8000
---------------------
Can you folks find anything wrong with this code?
The idea is to simply add up all the Hours that a particular employee worked
on a day specified by Current Date,
where the Attendance code is greater than 8000 indicating a Vacation day.
It sounds just perfect in theory, but in practice it totally is not working.
It is missing out the first day, and it somehow gets ridiculous values for
the SUM.
I wonder if there is something fundamentally wrong with the SQL.
-Gel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
