convert varchar to datetime

Last Post 04 Jun 2004 07:52 AM by ScottPletcher. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rboudwin
New Member
New Member

--
03 Jun 2004 03:48 PM
I am trying to see if OrderDate falls between two dates, startdate and enddate. The problem is that OrderDate is a varchar (argh). I am getting errors with the code below. OrderDate is stored mm/dd/yyyy HH:mm:ss even though it is a varchar. I welcome all suggestions.

<cfset startdate = dateFormat(dateAdd("d",-request.purchaseOrderLimit,now()),"mm/dd/yyyy")>
<Cfset enddate = dateFormat(dateAdd("d",1,now()),"mm/dd/yyyy")>

<cfquery name="check_Dup1" datasource="#request.Dsn#">
SELECT *
FROM ORDERS
WHERE CONVERT(datetime, OrderDate, 1) BETWEEN ' + #variables.startdate# + ' AND ' + #variables.enddate# + '
</cfquery>

ScottPletcher
New Member
New Member

--
04 Jun 2004 07:52 AM
You almost certainly have data in that column that is not a proper date.

You can skip those values like this. Btw, you should also use a date comparison not a character comparison, so get rid of the CONVERT on OrderDate:

SELECT *
FROM ORDERS
WHERE ISDATE(OrderDate) = 1 AND OrderDate BETWEEN '...' AND '...'



You could also look for bad dates like so:

SELECT *
FROM ORDERS
WHERE ISDATE(OrderDate) = 0

and fix them. Then to be sure you really ought to change the column to DATETIME so bad data can't get in there again
You are not authorized to post a reply.

Acceptable Use Policy