• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

MS SQL Update datefield from a form

I'm trying to update a datetime field from a form, I could update the date in the following format 04/01/2014 When I update the field to 04/01/2014 12:30:00 it errors out It only works if I enter 04/01/2014 and  doesn't work if I enter in the time. Database value 2014-04-01 00:00:00.000
0
smares323
Asked:
smares323
1 Solution
 
Dale BurrellDirectorCommented:
You might be in the wrong topic. The issue you are experiencing is almost certainly related to the form not SQL Server.

What have you built your form in, and where it the code?

If I am correct please change the topic to the technology which related to your form.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What is the field type set to?  Date or datetime or text or something else?

<<edit>>

Field type of the table.field
0
 
smares323Author Commented:
datetime field
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What serverside language are you using?
0
 
smares323Author Commented:
MS SQL 2000
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>I'm trying to update a datetime field from a form

Is this from a website? What server side language?  ASP.NET, ASP classic, PHP, something else?  Or is this a desktop app?
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If using a web form, did you try entering  "4/1/2014  3:25pm".   Before I add or update data to my db, I first check that the data is a date.     For example in  ASP classic you can use

if isdate("4/1/2014  3:25pm") then
    ' update the database

   else
   ' send an error message
end if  

Open in new window

0
 
smares323Author Commented:
ColdFusion mx 7
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I do not know CF, but it looks like there is an isDate function.  Try passing your variables through that before sending to the server.  http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/js/html/wwhelp.htm


<h3>IsDate Example</h3>
<cfif IsDefined("FORM.theTestValue")>
   <cfif IsDate(FORM.theTestValue)>
   <h3>The string <cfoutput>#DE(FORM.theTestValue)#</cfoutput> 
    is a valid date</h3>
   <cfelse>
   <h3>The string <cfoutput>#DE(FORM.theTestValue)#</cfoutput> 
    is not a valid date</h3>
   </cfif>
</cfif>
<form action = "isDate.cfm" method="post">
<p>Enter a string, find whether it can be evaluated to a date value.
<p><input type = "Text" name = "TheTestValue" value = "<cfoutput>#Now()#
</cfoutput>">
<input type = "Submit" value = "Is it a Date?" name = "">
</form>

Open in new window

0
 
_agx_Commented:
What does your cfquery code look like and what is the exact error message?  

It should work fine as long as
a) the input is a valid date string (your example is valid) AND
b) the target column is type "datetime"

Try using cfqueryparam to ensure you're passing a date object and protect against sql injection. Always a good idea w/SQL Server anyway. Be sure to validate the input string first, as Scott mentioned.  

(EDIT) Just keep in mind IsDate() is .. overly generous in what it considers a valid "date".  So if the source strings can vary, you may want to roll your own.

<!--- demo value --->
<cfset FORM.someDateString = "04/01/2014 12:30:00">

...  validate date string ...

<cfquery ....>
   UPDATE someTable
   SET      YourDateTimeCol = <cfqueryparam value="#FORM.someDateString#"
                                                 cfsqltype="cf_sql_timestamp">
   WHERE  .....
</cfquery>

Open in new window

0
 
smares323Author Commented:
Great advice
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now