How to change date format using datetime from openpyxl?

Question

I would like to create a function that will convert the date from a string variable to a date format for further processing. The problem is that I managed to isolate the fields where there is no entry "date" : [], but I can't create an if condition that would distinguish the date format string.

my code:

input: json file

{
    "entries": [
        {
            "attributes": {
                "cn": "Diana Troy",
                "sn": "Troy",
                "givenName": "Diana",
                "sAMAccountName": "wonder-woman",
                "userAccountControl": 514,
                "whenCreated": "2015-09-22 10:21:02+00:00",
                "whenChanged": "2023-01-11 09:33:59+00:00",
                "lastLogonTimestamp": [],
                "pwdLastSet": "2023-01-11 09:33:59.608543+00:00",
                "accountExpires": "9999-12-31 23:59:59.999999+00:00"
            },
            "dn": "CN=Diana Troy,OU=Users,OU=DC-COMICS,DC=universum,DC=local"
        }
    ]
}

code:

with open(encoded_retrieved_users, 'r', encoding="UTF-8") as file:
    data = json.load(file)
    retrieved_users = data['entries']
retrieved_users.sort(key=lambda d: d["attributes"]["sn"]) # sortuje po sAMAccountName

def is_value(var):
    if type(var) is int:
        val = var
    elif type(var) is str:
        val = var
    else:
        val = None
    return val

def to_short_date_format(string_to_format):
    if is_value(string_to_format) == None:
        short_date = "NO DATE"
    else:
        short_date = string_to_format
    return short_date

for user in retrieved_users:
    attributes = user['attributes']
    userAccountControl = is_value(attributes['userAccountControl'])

    whenCreated = to_short_date_format(attributes['whenCreated'])
    whenChanged = to_short_date_format(attributes['whenChanged'])
    lastLogonTimestamp = to_short_date_format(attributes['lastLogonTimestamp'])
    pwdLastSet = to_short_date_format(attributes['pwdLastSet'])
    accountExpires = to_short_date_format(attributes['accountExpires'])

    print("userAccountControl | " + str(userAccountControl) + " | " + str(type(userAccountControl)))
    print("whenCreated        | " + whenCreated + " | " + str(type(whenCreated)))
    print("whenChanged        | " + whenChanged + " | " + str(type(whenChanged)))
    print("lastLogonTimestamp | " + str(lastLogonTimestamp) + " | " + str(type(lastLogonTimestamp)))
    print("pwdLastSet         | " + str(pwdLastSet) + " | " + str(type(pwdLastSet)))
    print("accountExpires     | " + accountExpires + " | " + str(type(accountExpires)))
    print("----------------------------------")

output:

wonder-woman
userAccountControl | 514 | <class 'int'>
whenCreated        | 2015-09-22 10:21:02+00:00 | <class 'str'>
whenChanged        | 2023-01-11 09:33:59+00:00 | <class 'str'>
lastLogonTimestamp | NO DATE | <class 'str'>
pwdLastSet         | 2023-01-11 09:33:59.608543+00:00 | <class 'str'>
accountExpires     | 9999-12-31 23:59:59.999999+00:00 | <class 'str'>
----------------------------------

what i would like to get:

def to_short_date_format(string_to_format):
    if is_value(string_to_format) == None:
        short_date = "NO DATE"
    else:
        if string_to_format == <string format %Y-%m-%d %H:%M:%S%z>
            dt = datetime.strptime(string_to_format, '%Y-%m-%d %H:%M:%S%z')
            short_date = dt.strftime('%Y-%m-%d')
        elif string_to_format == <string format %Y-%m-%d %H:%M:%S.%f%z>
            dt = datetime.strptime(string_to_format, '%Y-%m-%d %H:%M:%S.%f%z')
            short_date = dt.strftime('%Y-%m-%d')
    return short_date

output:
    wonder-woman
    userAccountControl | 514
    whenCreated        | 2015-09-22
    whenChanged        | 2023-01-11
    lastLogonTimestamp | NO DATE
    pwdLastSet         | 2023-01-11
    accountExpires     | 9999-12-31
    ----------------------------------

Please read [the documentation](https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior) for the time format. Hint: `YYYY` is wrong. — deceze, Jan 23 '23 at 07:26

Kulasangar · Answer 1 · 2023-01-23T07:23:16.580

1

I was able to get the output by reusing your snippet. Minor catch is that the date format that you were trying to extract was wrong :

my_date = "2013-06-10 11:50:16+00:00"
# print(my_date)
# print(type(my_date))

from datetime import datetime
dt =datetime.strptime(my_date, '%Y-%m-%d %H:%M:%S%z')
print(dt)
dt_new = dt.strftime('%Y-%m-%d')

print(dt_new)

Output:

edited Jan 23 '23 at 07:23

answered Jan 23 '23 at 07:17

Kulasangar

9,046
5
51
82

thx for it, it's helped me to understand how convert string to date – Kubix Jan 23 '23 at 21:14

How to change date format using datetime from openpyxl?

1 Answers1