Between Function

[boatguy]

The other day I was trying to figure out how to dynamically convert my varchar field to a datetime field. I was able to do it in my view by using CAST(dbo.Entries.CreatedDate AS DATETIME) AS CreatedDate. The data now looks like 8/31/2005 1:11:40 PM.

The problem is, my query doesn't return any results even know the data exisits.

Here's my query:

<CFQUERY name="Betweendates" datasource="datasource">
SELECT EventTypeID, OrgID, Text, CreatedDate 
FROM VIEW_Entry_TodaysNotes
WHERE (CreatedDate BETWEEN '#DateNow#' AND '#Dateminusformat#') AND OrgID = '#ORGID#' 
</cfquery>

DateNow is formatted like: 08/31/2005 5:13:52 PM
Dateminusformat is formatted like 08/24/2005 5:13:52 PM

I tossed a Group By at the end of my query to get it to fail so I could show you the CF error code to check to see that the data looks ok:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'By'.


SQL = "SELECT EventTypeID, OrgID, Text, CreatedDate FROM VIEW_Entry_TodaysNotes WHERE (CreatedDate BETWEEN '08/31/2005 5:24:40 PM' AND '08/24/2005 5:24:40 PM') AND OrgID = '102' Group By"


Any suggestions?

 

[boatguy]

I figured a way around it by looking for dates greater than Dateminusformat. Not sure why between doesn't work so if anyone has an answer please share.

 

[r937]

the earlier date has to go first

r937.com | rudy.ca