Assigning Field value as empty- exacttarget AMP

Question

m trying to UPDATE :

IF @usmsopt == "False" THEN
  SET @smsoptoutdate = CURRENT DATE
ELSE
  SET @smsoptoutdate = CLEAR THE DATE

ENDIF

with the following AMP:

IF @usmsopt == "False" THEN
  SET @smsoptoutdate = Now()
ELSE
 SET @smsoptoutdate = ""
ENDIF

BUT WHEN @usmsopt == "True" then instead of emptying the field i get '1/1/1900 12:00:00 AM' value

any idea??

How about if i do like below ??: based on this:https://code.exacttarget.com/question/forcing-date-field-remain-null

IF @usmsopt == "False" THEN

  SET @smsoptoutdate = Now()

ELSE

 ENDIF

THE ABOVE (1) WORKED WELL FOR ME FOR CREATE OPERATION . BUT AM TRYING TO WORK STILL ON UPDATE WITHOUT TRYING TO CHANGE THE DATE FIELD AS TEXT IN DE . BASED ON THE LINK : https://code.exacttarget.com/question/end-date-field-getting-default-value

DOES THE FOLLOWING WORK (update an existing value to NULL):

/* to do : here i need to clear the old optout date */
ELSEIF @currentsmsopt == "True" AND @presmsopt =="False" THEN
IF NOT EMPTY(@presmsoptdt) THEN 
Var @deo
Set @deo = CreateObject("DataExtensionObject")
SetObjectProperty(@de, "CustomerKey", "DATA EXTENSION")
/*   erase the exsting value */
Var @smsoptoutdateproperty
Set @smsoptoutdateproperty = CreateObject("NullAPIProperty")
SetObjectProperty(@enddateproperty, "Name", "smsoptoutdt")

AddObjectArrayItem(@deo, "Properties", @smsoptoutdateproperty)

SET @result = UpsertData("DATA EXTENSION", 1, "custid", @ucustid, "emailaddress", @uemailaddress, "FirstName", @ufirstname, "LastName", @ulastname, "Zip", @uzip, "Mobile", @mobile1, "Phone", @phone, "Country", @ucountry, "Wants_email", @uemailopt, "Wants_sms", @currentsmsopt, "Add_Edit_Date", @editdt, "Optoutdate_sms", @smsoptoutdt, "Optoutdate_email", @emailoptoutdt, "Mobilecode", @countrycode, "Locale", @ulocale, "Signup", @signupdt, "Source", @source, "SubscriberKey", @friendkey)
ENDIF
ENDIF

Answer 1

The default time stamp in a data extension is the '1/1/1900 12:00:00 AM' value you are seeing.

In order to not get that, you would need to have the field set to a text field. This should correct your issue. The data will still be recognized in other parts of the program as a date, even though the field type is not set to date.

Answer 2

A bit of an old post but updating can be accomplished by pulling a reference value via a lookup then using that value to set the date in a target DE.

%%[
/* Lookup reference null value of dateStamp from ID=1 */
SET @n = Lookup('NullDateTest', 'dateStamp', 'ID', '1')
/* show that ID 2 has a value */
SET @n2 = Lookup('NullDateTest', 'dateStamp', 'ID', '2')
OUTPUT(concat("n1: ",@n, " n2: ", @n2, "<br>"))
]%%

%%[
/* show updated row count */

OUTPUT(UpdateData('NullDateTest', 1, 'ID', '2', 'dateStamp', @n))

]%%

%%[
/* show final values where date has been updated to null */
SET @n3 = Lookup('NullDateTest', 'dateStamp', 'ID', '1')
/* Used a lookup rows to prevent cached result of a lookup */
SET @n4 = Field(Row(LookupRows('NullDateTest', 'ID', '2'), 1), 'dateStamp')
OUTPUT(concat("<br>n3: ",@n3, " n4: ", @n4))
]%%

Oddly you cannot use a similar tactic directly in SSJS, perhaps because the way the concept of null works in SSJS but you can use an AMPscript var to perform the same action in SSJS.

%%[
/** reference null value pulled into Variable **/
SET @n = Lookup('NullDateTest', 'dateStamp', 'ID', '1')
]%%
<script runat=server>
Platform.Load("core", "1");
var DE = DataExtension.Init("NullDateTest");
DE.Rows.Update({dateStamp:Variable.GetValue("@n")}, ["ID"], ["2"]);
</script>