Select to view content in your preferred language

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

12607
15
03-22-2017 07:29 PM
SeanBirney
Emerging Contributor

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.

0 Kudos
15 Replies
SeanBirney
Emerging Contributor

You're right, it is ugly. But it worked perfect with and without the "/"s!

Thanks so much for the help

0 Kudos
by Anonymous User
Not applicable

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,

0 Kudos
TedKowal
Honored Contributor

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!

SeanBirney
Emerging Contributor

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

0 Kudos
DanPatterson_Retired
MVP Emeritus

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!

>>># a datetime 'now'
datetime.datetime(2017, 3, 23, 21, 34, 57, 717403)

>>> # looks ugly, but don't worry
>>> "{}-{:02.0f}-{:02.0f}".format(*[int(i) for i in (str(n).split(" ")[0]).split("-")])

'2017-03-23'

>>> # change the separator from '-' to '/' if you want

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

def pad_date(fld):
    """input a date field, strip off the time and format"""
    if fld is not None:
        lst = [int(i) for i in (str(fld).split(" ")[0]).split("-")]
        return "{}-{:02.0f}-{:02.0f}".format(*lst)
    else:
        return None
JayantaPoddar
MVP Esteemed Contributor

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).



Think Location