|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
April 22nd, 2005, 02:41 PM
|
|||
|
|||
|
Date manipulation problems - returns do data
Hi, I have the following query which works perfectly. It returns a single row of data for each staff member who has entered hours in the database, the hours are returned a sum of Hours for that user.
Code:
SELECT tblStaff.staffFname, tblStaff.staffLname, sum(tblTMDATA.hrs) as sHrs FROM tblStaff, tblTMDATA WHERE tblStaff.staffID=tblTMDATA.staffID GROUP BY tblStaff.staffFname, tblStaff.staffLname ORDER BY tblStaff.staffLname, tblStaff.staffFname Now I need to break this down and show it weekly (Friday - thursday) and monthly. tblTMDATA contains a date field however if i try to do anything with the date field say add " and wkEndingDTD = 4/22/2005" to the where clause, it brings back 0 records it should return 5 records. If I add it to the select, where, and group by it returns 1 record. Any ideas? my ultimate goal is to show the total hours grouped by staffmember ( john doe 40 ) for the week. with week defined as friday - thursday. And to show the total hours grouped by stafff member for the calendar month (john Doe 120 ) Second part is a separate query. But this is my goal
__________________
J. Birdsell,  www.carry-on-scheff-fans.com
|
|
#2
April 22nd, 2005, 03:57 PM
|
||||
|
||||
|
What database are you using?
MySQL? MS SQL? Oracle? " and wkEndingDTD = 4/22/2005" shouldn't really return any records because it's an actual date according to how dates are structured since all dates have a time attached to them (in your case, it most likely always be 12:00:00 AM). Try this: " and wkEndingDTD LIKE '%4/22/2005%'"
|
|
#3
April 22nd, 2005, 06:54 PM
|
|||
|
|||
|
Try using the ODBC date format:
and wkEndingDTD = #createODBCDate( '4/22/2005' )#
__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian. How to Post a Question in the Forums
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Date manipulation problems - returns do data |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
