1

Is it possible to compare two dates if they are formatted in different ways? One date is coming from a ColdFusion UI input which is in the following format:

mm/dd/yyyy

My timestamp in my MSSQL database is in the following format:

yyyy/mm/dd

Background: I need to write a query that compares the dates and returns rows with a timestamp after the user selected date. Would I need to reformat either date?

Example:

<cfset start_date = 'form.sDate'>

<cfquery name="sortDate" datasource="RC">
SELECT *
FROM my_db_table
WHERE Submission_Date > #start_date#
</cfquery>
Denoteone
  • 4,043
  • 21
  • 96
  • 150
  • 1
    Did you try? I am guessing that SQL Server will do the conversion for you but you will need to enclose your date in quotes, like `'#start_date#'` – Miguel-F May 13 '15 at 18:14
  • I did try without the quotes and it still returned everything. I will try it with the quotes and see what I get. Thanks for the feedback. +1 – Denoteone May 13 '15 at 18:15
  • You can convert the date from CF to yyyy/mm/dd using DateTimeFormat(todayDateTime,"yyyy/mm/dd") and then use https://wikidocs.adobe.com/wiki/display/coldfusionen/DateDiff – Anit Kumar May 13 '15 at 18:16
  • Thanks Miguel the single quotes are what was keeping it from working. If you want to put that in an answer I will accept it. – Denoteone May 13 '15 at 18:18
  • Just accept @Abram answer. He is correct that you should always use cfqueryparam in your queries. Glad it worked for you. – Miguel-F May 13 '15 at 18:29
  • RE: `` That would set the start date to the literal characters "F-O-R-M....." , not the value of the form field. Get rid of the quotes. Though FWIW, no need to copy the value to another variable. Just use the FORM variable. – Leigh May 13 '15 at 18:46
  • @Miguel-F - Yes, SQL Server usually convert strings implicitly, but .. [that can be both a blessing and curse](http://stackoverflow.com/questions/27049918/coldfusion-parameterizing-a-querie/27066113) - in any language ;-). IMO, it is better not to rely on it all. – Leigh May 13 '15 at 18:53
  • In addition to all comments and answers, change this, `` to this ``. – Dan Bracuk May 13 '15 at 19:46
  • 1
    @Leigh - great points as usual. Thanks. – Miguel-F May 13 '15 at 20:06

2 Answers2

5

First off, always use cfqueryparam in your queries when dynamically including content. Second, you should be able to use start_date "as is" to compare the date to the date in the database

So, something like:

<cfquery name="sortDate" datasource="RC">
    SELECT *
    FROM my_db_table
    WHERE Submission_Date > <cfqueryparam value="#start_date#" cfsqltype="cf_sql_date">
</cfquery>

Last, you can always test the raw sql in MSSQL Management Studio to test the date comparison.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Abram
  • 784
  • 5
  • 11
5

(Too long for comments...)

There are a few subtle distinctions here it is important to understand.

You are working with a string and a date object, not two dates. Also, SQL Server does not actually store dates in yyyy/mm/dd format. Dates are stored as big numbers internally. Since those are difficult for humans to process, whatever IDE you are using displays them in a more human friendly way, such as yyyy/mm/dd format. But again, that does not mean the database actually stores them as strings.

Since the datepicker value is a string, you should validate it first, then convert the string into a date object. Comparing apples and oranges (ie dates and strings) is common gotcha. That is almost always where date queries go awry. That is why converting the string into a date object is important. It ensures the database compares the same things ie two dates and that the results will always be what you expect.

Finally, use cfqueryparam to pass the value to your database correctly. Be sure to use the cfsqltype which corresponds to the db column: cf_sql_date (date only) or cf_sql_timestamp (date and time).

Technically, if the strings are always in mm/dd/yyyy format you can pass in the string "as is" and CF will automatically convert it for you. However, trusting the input will always be in the right format is not a great idea. Validating input and using cfqueryparam will ensure the most consistent results.

Community
  • 1
  • 1
Leigh
  • 28,765
  • 10
  • 55
  • 103