Todays date in MS SQL

[TonyP]

Hi, I know this is kinda a noob questions but i just cant figure it out.
I have a query that wants to pull all of todays data. No matter wht i use i cant seem to pull it out of the db.
I have MS SQL 2000 and CF 7.
My Where statement is as follows:
where paymentdate = #CreateODBCDate(now())#

I have also tried just now() and i have even tried this:
<cfset dttoday = CreateDate(
Year( dtNow ),
Month( dtNow ),
Day( dtNow )
) />
Then: where paymentdate = #CreateODBCDate(dttoday)#
No matter what i do i cant get anything to pull TODAYS info out of the DB.

FYI, in another query i can pull all of the data for the past 7 days like this:
where paymentdate Between #CreateODBCDate(DateAdd("d", -6, Now()))# AND #CreateODBCDate( dateadd("d",1,now()))#
And that works just fine. it even pulls todays data out correctly.

I just want only TODAYS data.
Any help would be appreciated.

Thanks

[paross1]

GETDATE
Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.

Syntax
GETDATE ( )

Return Types
datetime

Remarks
Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

In designing a report, GETDATE can be used to print the current date and time every time the report is produced. GETDATE is also useful for tracking activity, such as logging the time a transaction occurred on an account.

Examples
A. Use GET DATE to return the current date and time
This example finds the current system date and time.

SELECT GETDATE()


Phil

[Dan_Bracuk]

If your paymentdate values have time components greater than "00:00", change this:
Then: where paymentdate = #CreateODBCDate(dttoday)#
to this
Then: where paymentdate >= #CreateODBCDate(dttoday)#

[TonyP]

Ok, im still confused. How do you use a SELECT statement in a query where you have a WHERE looking for a date.
For example, here is my actual query:

SELECT tblpayments.for_auction, tblpayments.txn_type, tblpayments.paymentdate, tblpayments.payment_id, tblpayments.mc_gross, tblpayments.mc_fee, tblitems.item_price, tblitems.item_name, tblcustomers.Cust_First, tblcustomers.Cust_Last
FROM (tblitems INNER JOIN tblpayments ON tblitems.txn_id = tblpayments.txn_id) LEFT JOIN tblcustomers ON tblpayments.customer_id = tblcustomers.Cust_ID
where paymentdate = #CreateODBCDate(now())#
AND payment_status = 'Completed'
AND for_auction <> 1

I dont follow how i would put a SELECT GETDATE in that query.

Tony

[JR__Bob__Dobbs-qSBHQ2]

Bear in mind that in SQL Server the DATETIME datatype always contains the time, you'll need to specify your WHERE clause to look for datetime values starting at midnight today and ending prior to midnight the next day.

I also recommend that you use cfqueryparam in your queries.

<cfset startDate="#DateFormat(Now(), 'mm/dd/yyyy')# 00:00:00" /> <!--- today at midnight --->
<cfset endDate="#DateFormat(DateAdd('d', 1, startDate), 'mm/dd/yyyy')# 00:00:00" /> <!--- the next day at midnight --->

SELECT mydata
FROM mytable
WHERE paymentdate >= <cfqueryparam value="#startDate#" cfsqltype="cf_sql_timestamp" />
AND paymentdate < <cfqueryparam value="#endDate#" cfsqltype="cf_sql_timestamp" />

[TonyP]

That worked perfect. Thank You!
It seems that i was trying to pull todays date with one var and instead for MS SQL you need to use a range as you said, like from midnight to midnight tonight.
Worked great. Thanks again.