help! simple SQL problem (probably an ms access issue?)

hi guys,

i'll keep this brief, as i assume my problem can be overcome easily with a little know-how from someone here.

screen shot of db: http tinyurl.com/377d9e - stupid forum rules won't let me post a url?!

i'd like to pull the total number of distinct sessions (sessionid) from my table (minlog) that start (xdate) between 12pm and 12am i.e. daily hits.

so far i have this:

<cfquery name="q6" datasource="acomxp">
		SELECT DISTINCT minlog.sessionid
		FROM minlog 
		WHERE DATEPART("h",minlog.xdate) >= 12 AND DATEPART("h",minlog.xdate) <= 24;
		</cfquery>

      <td><cfoutput>#q6.recordcount#</cfoutput></td>

Code (markup):

i'm working with coldfusion and ms access - i believe this is why i'm having an issue getting it to work.

my error message is:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in C:\ColdFusion8\wwwroot\index.cfm: line 114

112 : <td>shortDay</td>
113 : <td><cfoutput>#equalOutcomes#</cfoutput></td>
114 : <cfquery name="q6" datasource="acomxp">
115 : SELECT DISTINCT minlog.sessionid
116 : FROM minlog


thanks for the help in advance,
lewis

 

might i add, before someone mentions it. 'q6' is a unique query name. i've also checked all my table and field names are correct.

cheers,
lewis

 

Either DatePart('h', xDate) or Hour(xDate) would work. Note the single quotes around 'h'.

But the hour value will never be 24. The range is from 0 to 23. Also if you want records for a single date, you'll need to add a date filter as well.

SELECT DISTINCT minlog.sessionid
FROM minlog 
WHERE DATEPART('h',minlog.xdate) >= 12 
AND     DATEPART('h',minlog.xdate) <= 23;


SELECT DISTINCT minlog.sessionid
FROM minlog 
WHERE Hour(minlog.xdate) >= 12 
AND     Hour(minlog.xdate) <= 23;

Code (markup):

 

cheers cfStarlight,

got that working a treat. thanks also for the 0-23 tip, 0 obviously counting as 24 output. although there's nothing wrong with using:

      WHERE (HOUR(xdate) >= 12 AND HOUR(xdate) <= 24)

Code (markup):

...is there? would it be bad practice to use 24 here instead of 23, knowing full well that 24 would never come out? i get the same output/results either way.

cheers man,
lewis

 

If what you want is records between 12 noon and 11:59:59.999 PM, then no it wouldn't make a difference.

Personally I would use <= 23, if only because <= 24 implies that the results should contain an hour 24 and someone viewing the code/data might suspect there's something wrong because hour 24 is missing. But that's entirely a personal preference

 

hey man,

yea, i totally see what you mean.

00:00:00 - 11:59:59

      WHERE (HOUR(xdate) >= 0 AND HOUR(xdate) < 12)

Code (markup):

12:00:00 - 23:59:59

      WHERE (HOUR(xdate) >= 12 AND HOUR(xdate) < 24)

Code (markup):

or

00:00:00 - 11:59:59

      WHERE (HOUR(xdate) >= 0 AND HOUR(xdate) <= 11)

Code (markup):

12:00:00 - 23:59:59

      WHERE (HOUR(xdate) >= 12 AND HOUR(xdate) <= 23)

Code (markup):

...doing exactly the same thing.

i'm gonna go with the first one because it uses less code and fits better in a 24hr clock context. i can tell you're more than a programmer than i am, haha.

ta man,
lewis

 

Hey if you want really short use BETWEEN

WHERE HOUR(xdate) BETWEEN 0 AND 11

 

Though that means you would have to use (0 AND 11) ... and ... (12 AND 23). The reason being BETWEEN is inclusive.

So

WHERE HOUR(xdate) BETWEEN 0 AND 12

would give you

00:00:00 - 12:59:59

 

saaaweet! didn't think of that. you're on the ball this morning.

cheers man,
lewis