Date Update Problem in SQL 2000 Database

[MinalMaurice]

I am migrating from Microsoft Access to Microsoft SQL as my database.

I'm getting problems with dates - an error is returned "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

This is due to the line of code

Set LastVisit = '#TodayDate#' Where ID = #MemberResponse.ID#

based on
<CFSET TodayDate = '#dateformat(Now(), 'dd/mm/yyyy')#'>

SQL 2000 doesn't like unlimited dates, so that the above CFSET needs to be changed to prevent the error occurring.

Any ideas on how to modify that CFSET?

Thanks in advance.

 

[TruthInSatire]

try

set lastVisit = ###todayDate### where ID = #memberResponse.id#

also, your cfset has unneeded quotes and pound signs.

for best practice your cfset should look like this...
<CFSET TodayDate = dateformat(Now(), 'dd/mm/yyyy')>

Beware of programmers who carry screwdrivers.

 

[ecobb]

Don't worry about the cfset statement.

Set LastVisit = <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_DATE"> 
Where ID = #MemberResponse.ID#

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

doh, keep forgetting about cfqueryparam in fixing "data type mismatch" errors. good post.

Beware of programmers who carry screwdrivers.

 

[MinalMaurice]

Thanks - that worked a treat.

Are there any other examples of SQL_Type that I should be urgently aware of?

 

[ecobb]

Pretty much any type that you would be inserting into a database.

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm#wp1102474

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher