I remember a couple of years ago working with time in MySQL and using "between" for finding a date/time that was earlier than and endDate and later than a startDate.
as in: where newDate between startDate and endDate Perhaps that will help. I don't know how MySQL will respond to that date-time format, however. Just give it a try. It seems that if MySQL will accept entry of that format into a database, then it should be able to read it coming out. Rick -----Original Message----- From: Nick Sweeney [mailto:[email protected]] Sent: Wednesday, August 22, 2012 2:22 PM To: cf-newbie Subject: Search MySQL Database - irregular DateTime Format 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:5906 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
