Nick, Use the CreateODBCDateTime to set the start time to the date and time to 00:00:00, then the end as the end date and 23:59:59 and use the between in the query.
-- Regards, Andrew Scott WebSite: http://www.andyscott.id.au/ Google+: http://plus.google.com/113032480415921517411<http://plus.google.com/108193156965451149543> On Thu, Aug 23, 2012 at 4:22 AM, Nick Sweeney <[email protected]>wrote: > > Hi everyone - > > I have some dates and times stored in a database and I need to search it. > (Ideally exact and inbetween searches - Like - Everything after #myDate#) > > However - the data stored in the DB isn't in a standard format - the dates > look like: 2008-10-01T12:00 (Not standard to me anyway...) > > I know in CF I can get to either the Date or Time by doing: > > <cfset variables.thisDateTime = "2008-10-01T01:35"> > <ul> > <li>Time: #TimeFormat(RIGHT(variables.thisDateTime, 5), 'H:mm tt')# > </li> > <li>Date: #DateFormat(LEFT(variables.thisDateTime, 10), > 'mmm-dd-yyyy')</li> > </ul> > > But How do I do a Database Search on that? > > I have a convoluted way of making the date match format from a FORM as > follows: > > <cfset variables.DateTimeMin = "#FORM.DepartStartDay#T#FORM.StartTime#"> > <cfset variables.DateTimeMax = "#FORM.DepartEndDay#T#FORM.EndTime#"> > > But How do I search the DB?? I would imagine I want to use standard Date > Time formatting - but the data in the DB doesn't match... ?? > > <cfquery name="qryTeamSchedule" datasource="#Application.DSN#" > username="#Application.username#" password="#Application.password#"> > SELECT MyData > FROM MyDB > WHERE 1=1 > > AND (SavedDateTime > <cfqueryparam > cfsqltype="cf_sql_timestamp" value="#variables.DateTimeMin#"> > AND SavedDateTime < <cfqueryparam cfsqltype="cf_sql_timestamp" > value="#variables.DateTimeMax#">) > > Order By Whatever ASC > </cfquery> > > Thoughts? - THANK YOU! > > - Nick > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5907 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
