nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Date (date field - mm/dd/yyyy) to String (string field - yyyymmdd) with vbscript in field calculator?

SeanBirney

Sounds simple but....trying to convert multiple date entries (mm/dd/yyyy) in a date field to a string field with yyyymmdd format. I know it can be done with tbx but need a simple line of vbscript (not python) for this action. Is this possible? Lots of string to date solutions but not so much the other way. Thanks for the ideas.

Trouver plus de publications taguées avec

Accepted answers

All comments

TedKowal

I love VB and lost the battle to have it remain within ArcGIS, but when it comes to formatting keep in mind Dan Patterson‌ example with Python, it is magnitudes better in formatting than VB could ever be! My solution is simply a hack!

To make the above less ugly another old trick for leading zero's came back mind it is also useful in variant way with Nulls......

To force leading zeros on a string in VBSCRIPT Right("00" & month(Now),2) this could simplify some of the less than 10 tests....

Not forget the bonus null vb hack!

Say myTestString could be null and you do not want to deal separately for null values.....

if trim(myTestString & "") = "" then ...... <-- this appends a blank space to a null and now you can treat nulls in the strings as a space.....

PS... I have been using Python.Net in all my VB.Net for formatting functions -- it is that good!

Anonymous User

This was beautiful and saved me in a pinch. I have a chart that has date values and they are showing with an unwanted time stamp. This allowed me to reformat into a sortable text value that makes good labels. Sort nice, look nice on the x axis of the graph.

Thank you,

JayantaPoddar

Another one in Field Calculator would be

Right( [Date_MDY],4 ) & Left( [Date_MDY],2 ) & Mid( [Date_MDY], 4, 2 )

The format has to be consistent (MM/DD/YYYY for the above example).

TedKowal

I have no experience or familiarity with Attribute Assistant, however if you want it embedded within a IIF statement, MS access if the closest I have that contains the IIF function....

Putting this into a single line IIF format is ugly .... (ReformatDateString is a value field)

<SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"yyyy"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="string token">"/"</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="token function">IIf</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"m"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="operator token"><</SPAN><SPAN class="number token">10</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"0"</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"m"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="string token">"/"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"m"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="string token">"/"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="token function">IIf</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"d"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="operator token"><</SPAN><SPAN class="number token">10</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"0"</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"d"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">DatePart</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"d"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token function">CDate</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>inspdate<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

I assume that python doesn't work in your environment? as per my example from late yesterday

SeanBirney

Nothing is Something! Any thoughts on incorporating the code block (keeping leading zeros) into an "IIF" statement that could be used as an expression (only VB) in the 'Attribute Assistant'? I just need to suck that date with zeros into another field. Not too sure of the syntax that would be needed.

Thanks

CodeBlock:
strDate = CDate([BirthDate])
strDay = DatePart("d", strDate)
strMonth = DatePart("m", strDate)
strYear = DatePart("yyyy", strDate)
If strDay < 10 Then
strDay = "0" & strDay
End If
If strMonth < 10 Then
strMonth = "0" & strMonth
End If
FormattedDate = strYear & "/" & strMonth & "/" & strDay

Bottom Window:
FormattedDate

TedKowal

ahhhh .... the different ways of treating nothingness! Who would of thought the concept of nothing was this difficult. I only brought up Null time values because Sean asked specifically for a vbscript and the time functions will fail if a null date is encountered....

DanPatterson_Retired

If it is a field calculation that you need, you can convert a datetime object to a string, split it into its component parts, reformat, reassemble and pad the necessary bits. This is a python example since it can be used in ArcGIS PRO since VB is no longer supported in that environment.

Here is a online example... 'n' is a datetime object... in the following you would replace 'n' with !YourDateFieldName!

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> n&nbsp; <SPAN class="comment token"># a datetime 'now'</SPAN>
datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2017</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">23</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">21</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">34</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">57</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">717403</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># looks ugly, but don't worry</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="string token">"{}-{:02.0f}-{:02.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN><SPAN class="punctuation token">[</SPAN>int<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">" "</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"-"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="string token">'2017-03-23'</SPAN>

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># change the separator from '-' to '/' if you want</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Or if a one-liner... which I don't recommend ... is not to your taste, then produce a def .. I just used a couple of dates and a null date for testing

The full code is below since one line got truncated. You can replace the separator as you like and you can alter the order of year, month, date with few modifications

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">pad_date</SPAN><SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="string token">"""input a date field, strip off the time and format"""</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> fld <SPAN class="keyword token">is</SPAN> <SPAN class="operator token">not</SPAN> None<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lst <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>int<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">" "</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"-"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> <SPAN class="string token">"{}-{:02.0f}-{:02.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>lst<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> None<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

SeanBirney

Thanks so much for all the ideas. I do need to keep the leading zeros so the code block solution did the trick. I'm just getting familiar with Attribute Assistant and need to incorporate the leading zero code into the DynamicValue Table/Value Info Field. Is this possible considering it uses the codeblock option? Is there a trick to incorporating that code into the Value Info field?

Thanks again

DanPatterson_Retired

Ted... more fodder for numpy is nullness

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">from</SPAN> datetime <SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> dt
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> n <SPAN class="operator token">=</SPAN> dt<SPAN class="punctuation token">.</SPAN>now<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> n
datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2017</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">23</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">13</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">48</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">21</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">718178</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp;&nbsp; <SPAN class="comment token"># a standard datetime object</SPAN>

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">import</SPAN> numpy <SPAN class="keyword token">as</SPAN> np&nbsp;&nbsp; <SPAN class="comment token"># now lets look at numpy datetime implementation</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># ---- introducing --- Not a Number (NaN) and ... Not a Time (NaT) ......</SPAN>

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> not_a_time <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>datetime64<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'NaT'</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token"># hang on... NaN has a twin in time NaT </SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> not_a_time
numpy<SPAN class="punctuation token">.</SPAN>datetime64<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'NaT'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># do some type conversions to check for 'none-ness'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> not_a_time<SPAN class="punctuation token">.</SPAN>astype<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'int8'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="token boolean">True</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># now let the good times roll by converting out standard datetime to np.datetime</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> good_time <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>datetime64<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">)</SPAN>
numpy<SPAN class="punctuation token">.</SPAN>datetime64<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'2017-03-23T13:48:21.718178'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># type conversion again</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> good_time<SPAN class="punctuation token">.</SPAN>astype<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'int8'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="token boolean">False</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># you will always have a good time, when numpy is around...</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

TedKowal

perhaps if you want a formatted string with leading zeros ... use the show codeblock option. In any event the above vbscript code (and mine as well) will fail if NULL Date values are found and your will have to handle your logic for missing dates!

CodeBlock:
strDate = CDate([BirthDate])
strDay = DatePart("d", strDate)
strMonth = DatePart("m", strDate)
strYear = DatePart("yyyy", strDate)
If strDay < 10 Then
strDay = "0" & strDay
End If
If strMonth < 10 Then
strMonth = "0" & strMonth
End If
FormattedDate = strYear & "/" & strMonth & "/" & strDay

Bottom Window:

FormattedDate

sample output "2017/03/05"

EdwinRoa

You can use:

Year( [DateField] ) & "/" & Month( [DateField] ) & "/" & Day( [DateField] )

Year( [DateField] ) & Month( [DateField] ) & Day( [DateField] )

DanPatterson_Retired

for a python solution should someone need it... consider

n = a datetime.datetime object (ie datetime.now() in any format

<SPAN class="string token">"{}{:02.0f}{:02.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">.</SPAN>year<SPAN class="punctuation token">,</SPAN> n<SPAN class="punctuation token">.</SPAN>month<SPAN class="punctuation token">,</SPAN> n<SPAN class="punctuation token">.</SPAN>day<SPAN class="punctuation token">)</SPAN>
<SPAN class="string token">'20170322'</SPAN>
<SPAN class="comment token"># or</SPAN>
<SPAN class="string token">"{}-{:02.0f}-{:02.0f}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">.</SPAN>year<SPAN class="punctuation token">,</SPAN> n<SPAN class="punctuation token">.</SPAN>month<SPAN class="punctuation token">,</SPAN> n<SPAN class="punctuation token">.</SPAN>day<SPAN class="punctuation token">)</SPAN>&nbsp; <SPAN class="comment token"># with separators</SPAN>
<SPAN class="string token">'2017-03-22'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.