date formatting and SQL
I've been having a few issues with dates and SQL recently so I thought I would quickly post my solution. When searching in SQL with dates this is what I tend to use:
<!--- vars in: dateFrom, dateTo --->
<cfset createODBCDate(form.dateFrom)>
<cfset createODBCDate(form.dateTo)>
<cfquery name="qGetComments"datasource="#application.defaultProperties.datasourceName#">
SELECT comments.comment, comments.commentDate, tbluser.firstName, tbluser.lastName
FROM comments INNER JOIN
tbluser ON comments.staffID = tbluser.userID
WHERE (comments.studyID = #qStudyInfoList.studyId#) AND
comments.commentDate BETWEEN #LSParseDateTime(form.dateFrom)# AND #LSParseDateTime(form.dateTo)#
order by commentDate
</cfquery>
<cfset createODBCDate(form.dateFrom)>
<cfset createODBCDate(form.dateTo)>
<cfquery name="qGetComments"datasource="#application.defaultProperties.datasourceName#">
SELECT comments.comment, comments.commentDate, tbluser.firstName, tbluser.lastName
FROM comments INNER JOIN
tbluser ON comments.staffID = tbluser.userID
WHERE (comments.studyID = #qStudyInfoList.studyId#) AND
comments.commentDate BETWEEN #LSParseDateTime(form.dateFrom)# AND #LSParseDateTime(form.dateTo)#
order by commentDate
</cfquery>
This helps when people are using differnt date formats as well.
Comments
[Add Comment]
If you use cfqueryparam to pass the date instead, that will eliminate the need for both the createODBCDate() and LSParseDateTime() functions.
# Posted By Eric Cobb
| 14/01/08 14:20
| Report abusive comment
Thanks Eric, thats makes sense. Note to self: follow procedure!
# Posted By nick tong
| 14/01/08 14:23
| Report abusive comment
[Add Comment]
