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

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

SeanBirney — Thu, 23 Mar 2017 02:29:48 GMT

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.

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

DanPatterson_Retired — Sun, 12 Dec 2021 05:26:21 GMT

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

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

"{}{:02.0f}{:02.0f}".format(n.year, n.month, n.day)
'20170322'
# or
"{}-{:02.0f}-{:02.0f}".format(n.year, n.month, n.day)  # with separators
'2017-03-22'‍‍‍‍‍

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

EdwinRoa — Thu, 23 Mar 2017 04:19:29 GMT

You can use:

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

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

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

TedKowal — Thu, 23 Mar 2017 14:59:53 GMT

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"

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

DanPatterson_Retired — Sun, 12 Dec 2021 05:26:24 GMT

Ted... more fodder for numpy is nullness

>>> from datetime import datetime as dt
>>> n = dt.now()
>>> n
datetime.datetime(2017, 3, 23, 13, 48, 21, 718178)   # a standard datetime object

>>> import numpy as np   # now lets look at numpy datetime implementation
>>>
>>> # ---- introducing --- Not a Number (NaN) and ... Not a Time (NaT) ......

>>> not_a_time = np.datetime64('NaT')  # hang on... NaN has a twin in time NaT 
>>> not_a_time
numpy.datetime64('NaT')
>>>
>>> # do some type conversions to check for 'none-ness'
>>>
>>> not_a_time.astype('int8') == 0
True
>>>
>>> # now let the good times roll by converting out standard datetime to np.datetime
>>>
>>> good_time = np.datetime64(n)
numpy.datetime64('2017-03-23T13:48:21.718178')
>>>
>>> # type conversion again
>>> good_time.astype('int8') == 0
False
>>>
>>> # you will always have a good time, when numpy is around...‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

SeanBirney — Thu, 23 Mar 2017 23:08:56 GMT

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

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

DanPatterson_Retired — Sun, 12 Dec 2021 05:26:26 GMT

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!

>>> n  # 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‍‍‍‍‍‍‍

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

TedKowal — Fri, 24 Mar 2017 14:28:41 GMT

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

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

SeanBirney — Fri, 24 Mar 2017 17:59:53 GMT

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

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

DanPatterson_Retired — Fri, 24 Mar 2017 18:35:40 GMT

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

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

SeanBirney — Fri, 24 Mar 2017 19:55:59 GMT

No, I'm working with an Add-In called Attribute Assistant which is VB centric (possible python support in the future). Thanks, though, useful examples are always welcome.

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

TedKowal — Fri, 24 Mar 2017 20:22:20 GMT

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)

DatePart("yyyy",CDate([inspdate])) & "/" & IIf(DatePart("m",CDate([inspdate]))<10,"0" & DatePart("m",CDate([inspdate])) & "/",DatePart("m",CDate([inspdate])) & "/") & IIf(DatePart("d",CDate([inspdate]))<10,"0" & DatePart("d",CDate([inspdate])),DatePart("d",CDate([inspdate])))‍‍‍

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

JayantaPoddar — Fri, 24 Mar 2017 20:34:42 GMT

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

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

SeanBirney — Fri, 24 Mar 2017 23:39:55 GMT

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

Thanks so much for the help

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

Anonymous User — Thu, 22 Feb 2018 21:04:59 GMT

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,

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

TedKowal — Fri, 23 Feb 2018 18:08:23 GMT

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!