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

9582
15
03-22-2017 07:29 PM
SeanBirney
New Contributor II

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
DanPatterson_Retired
MVP Esteemed Contributor

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'
EdwinRoa
Esri Contributor

You can use:

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

or 

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

TedKowal
Regular Contributor II

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"

DanPatterson_Retired
MVP Esteemed Contributor

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...
TedKowal
Regular Contributor II

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

0 Kudos
SeanBirney
New Contributor II

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

DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
SeanBirney
New Contributor II

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.

0 Kudos
TedKowal
Regular Contributor II

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])))‍‍