getdate problem 1

[grx21]

Hello,
I am designing a page that will display how many new contacts have been received "today". Currently there are 5 new contacts in the database however, when I count them it returns that there are 0 contacts for today when in fact there are 5 of them. I am using MS SQL Server. The DATE_CREATED DataType is datetime and the defaultvalue is(getdate()). I had done something similar with access (using Date() instead than getdate()) and it worked i just don't understand why it is not working with SQL Server.

Here is the query:

<cfquery name=&quot;Today_Contact_Count&quot; datasource=&quot;contacts&quot;>
SELECT COUNT (CONTACTS.DATE_CREATED) AS TODAY_CONTACTS_COUNT
FROM CONTACTS
WHERE (CONTACTS.DATE_CREATED = (getdate()));
</cfquery>

Thank you,

Grx21

 

[CFDude]

The getDate returns the day and time, so most likely it will never return anything, (unless someone happens to join at the exact same time you run the query

Try using

WHERE (CONTACTS.DATE_CREATED > #CreateOdbcDate(Now()-1)#);

 

[grx21]

Thanks CFDude I will give it try and let you know and also thank you for answering my post I appreciate it. One more question, why did it work in access then?

 

[CFDude]

I don't play much with access, but my guess is that Date() returns only the day, month and year with no time, so it may find a match just on those parts of the date. The GetDate() function in SQL Server returns the time as well, so it would be harder to find a match.

 

[grx21]

O.K. it worked! Thank YOU very much for your help CFDude!

grx21

 

[grx21]

Alright, one more question. To try to display the number of the contacts for &quot;yesterday&quot; I wrote the where clause like this:

WHERE (CONTACTS.DATE_CREATED < #CreateOdbcDate(Now()-1)#);

However, it will count the one from yesterday and all of the rest of the contacts entered prior to &quot;yesterday&quot;. What would I need to do to just display the ones for yesterday only?

GRX21

 

[CFDude]

Glad to see it worked. To restrict it even more, add another clause :

WHERE (CONTACTS.DATE_CREATED < #CreateOdbcDate(Now()-1)#
AND CONTACTS.DATE_CREATED > #CreateOdbcDate(Now()-2))
;

 

[grx21]

Alright, I will give it a try and let you know. Thanks again

Grx21