CODEWITHSUNDEEP
sql-serverdatecoldfusion
Denoteone
Denoteone

Reputation: 4055

Date Picker format vs Database timestamp

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>

Upvotes: 1

Views: 393

Answers (2)

Leigh
Leigh

Reputation: 28873

(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.

Upvotes: 5

Abram
Abram

Reputation: 804

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.

Upvotes: 5

Related Questions