This is no problem
First, setup a variable as such:
<@IFEMPTY <@ARG date>>
<@ASSIGN request$sql_date_value “NULL”>
<@ELSE>
<@ASSIGN request$sql_date_value “<@SQ><@ARG DATE><@SQ>”>
</@IF>
Then use the variable in the Update SQL action (Insert and Select also work).
The trick is that you must set the Quote Value on this column to False. This
tells Witango to use the variable content as is (which explains the need for
the <@SQ>s) rather than attempting to format the date automatically.
Note that if the argument date is human enterable, you’ll want to do a few
checks on the value first to ensure that it’s a valid date, in the correct
format that your SQL server expects, etc. Explicitly stripping single-quotes
would be a good idea.
Also, this would effectively create the value ‘9/13/2010’ which most SQL
servers can deal with, however it is more appropriate to use native formats. If
I remember MS SQL properly date format is: YYYY-mm-ddTHH:MM:SS:mmm
Another good idea is to use the ODBC date literal format: { d ‘YYYY-mm-dd’ }
(this is what Witango converts dates to behind the scenes)
The point is that by turning off Quote Value, formatting of the value is turned
over to you and you must ensure that it is correct.
Robert
PS. The value of “NULL” does not get quoted.
From: WebDude [mailto:[email protected]]
Sent: Monday, September 13, 2010 1:46 PM
To: [email protected]
Subject: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
Hello,
Can this be done? I have a table where they need to sort by date. The column is
a datetime filed to sort dates correctly. we also need to be able to update the
date, delete the date (make null) or keep the date null on an insert. Keeping
the dat null on insert is fairly easy, but what if they want to sdelete the
date? Adding blank form field defaults to 1/1/1900 in the db. Can an insert of
NULL be written in SQL? I tried he obvious... <@IFEMPTY <@ARG
date>>NULL<@ELSE><@ARG date>>/@IF>, <@IFEMPTY <@ARG
date>>DbNull.value<@ELSE><@ARG date>>/@IF>, etc...
Has anyone done this before?
Thanks!
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
----------------------------------------
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
