MS query on SQL database date paramaters

[emmy]

I have a SQL database, I've written a report in crystal reports that works great. I want to pull the same data into excel. If I use the same sql in MS Query I get errors on the date. Here is the code as it is used in Crystal, can someone help me make it MS Query compatible?

SELECT     tps_user.tps_title AS Analyst, COUNT(im_incident_closure.im_guid) AS ClosedIncidents, tps_group.tps_title AS PrimaryGroup 
FROM         tps_user INNER JOIN
                      im_incident_closure ON tps_user.tps_guid = im_incident_closure.im_creation_user_guid INNER JOIN
                      tps_analyst ON tps_user.tps_guid = tps_analyst.tps_guid INNER JOIN
                      tps_group ON tps_user.tps_primary_group_guid = tps_group.tps_guid
WHERE (CAST(LEFT(DATEADD(hh, -8, im_incident_closure.Im_creation_date) , 19) AS smalldatetime) >= {?BeginDate}) AND  (CAST(LEFT(DATEADD(hh, -8, im_incident_closure.Im_creation_date) , 19) AS smalldatetime) <= {?EndDate}) 
GROUP BY tps_user.tps_title, tps_group.tps_title

 

[SkipVought]

Hi,

"I get errors on the date."

Error message? Error value?

Please explain more completely.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Give this a try, if I understood your code...

SELECT
  tps_user.tps_title                  AS Analyst
, COUNT(im_incident_closure.im_guid)  AS ClosedIncidents
, tps_group.tps_title                 AS PrimaryGroup 

FROM       
            tps_user
INNER JOIN im_incident_closure
   ON tps_user.tps_guid               = im_incident_closure.im_creation_user_guid 
INNER JOIN tps_analyst 
   ON tps_user.tps_guid               = tps_analyst.tps_guid 
INNER JOIN tps_group 
   ON tps_user.tps_primary_group_guid = tps_group.tps_guid
[b]
WHERE im_incident_closure.Im_creation_date >= DateValue([BeginDate])
  AND im_incident_closure.Im_creation_date <= dateValue([EndDate])
[/b]
GROUP BY tps_user.tps_title, tps_group.tps_title

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[emmy]

Skip, that's closer than I've gotten on my own. The error I get with the datevalue function is "Datevlue is not a recognized built-in function name. Statement could not be prepared." I suppose it's because this is a SQL database so I'm trying to compare a date-time field to a date only. Added to this is the fact that the date is stored in GMT and I'm in Pacific time, so I'm trying to compensate for that as well. Also I have years of data but want to pull only a month at a time, I would prefer to do that server-side and return only the date range requested rather than pull all the data then have excel filter it.
Thanks for your help.

 

[SkipVought]

Here's the issue for YOU to solve with the correct SQL Server syntax.

The "date" parameters are TEXT. Either the TEXT value must be converted to a Date value OR the the Date Field value must be converted to TEXT.

The caveat in the latter approch is that the TEXT must colate correctly since you are using >= <=. So the TEXT must be in some kind of yyyy mm dd format.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]