ArcMap Field Calculator: ArcPy Date to Decimal Function

One of the standards in our databases is to store dates as 8-digit integer values in the format of yyyymmdd. This requires us to occasionally convert values from date fields into this format.

We can do this in the ArcMap Field Calculator using this arcpy function:

def datetodouble(inNum):
     splitList = str(inNum).split("/")
     return  splitList [2]  +("0"+ splitList [0])[-2:]  +("0"+ splitList [1])[-2:]

Entering a Null Value in VB.Net DateTimePicker

While working on a VB.Net form to query a database that includes an optional date range query, I decided I wanted to display a datetimepicker with a blank value by default and then, if the user sets a date show the date.  By default, a datetimepicker can not have a Null date, so I went a’Googling and found this post, .NET Reference: Setting the DateTimePicker to a Blank Value ,that had exactly the technique I wanted.

Basically, I set the datetimepicker’s CustomFormat to a space (” “) when I want it to be Null and then when the user makes a selection, set the custom format to the date format I wanted to display.    This is the sample code I found:

Private Sub ToDateField_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles ToDateField.ValueChanged
     Me.ToDateField.Format = Windows.Forms.DateTimePickerFormat.Custom
     Me.ToDateField.CustomFormat = "M/dd/yyyy"
End Sub

To let the user delete the date they entered, I add this subroutine that handles the KeyDown event for the datetimepicker. If the user hits delete or backspace, I set the format back to my null CustomFormat.

Private Sub ClearDates_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) _
 Handles ToDateField.KeyDown

 If (e.KeyCode = Windows.Forms.Keys.Delete) Or (e.KeyCode = Windows.Forms.Keys.Back) Then
 Dim mDateTimePicker As Windows.Forms.DateTimePicker
          mDateTimePicker = TryCast(sender, Windows.Forms.DateTimePicker)
          If Not (mDateTimePicker Is Nothing) Then
               mDateTimePicker.CustomFormat = " "
          End If
End If
End Sub

When building my SQL statement, I check the .CustomFormat property to determine whether or not to include a condition for the data:

 If Not (Me.ToDateField.CustomFormat = " ") Then
 'Code here to add to my SQL statement
 End If