How to change timestamp in Db2?

Question

I have created a table called 'Chicago crime data' I need to do some queries on it but the table wont load. The message i get is (timestamp format does not match data in the column 'UPDATEDON')

What is populated in the Date column is MM-DD-YYYY, I need to change it to DD-MM-YYYY HH:MM:SS. I have tried selecting the DD-MM-YYYY under the dropdown of Date Format and then the HH:MM:SS under Timestamp format, but when i complete and begin the load, it says the load succeeded but all 533 rows rejected and when i attempt to view the table (understandably) it says there is no data here yet.

To my understanding you should be able to do a custom change on the time format, but i cant see how? Any help would be much appreciated. Thanks.

Answer 1

I had this same problem on this IBM course - I found that the way around it was to try to paste something into the Timestamp format box on db2. This worked even though I previously hadn't been able to type in that box, and once I had pasted something I was able to type in that box again.

Answer 2

I too faced the same issue. I fixed it by as below:

1. Paste some random text into TIMESTAMP dropdown field, then you edit option will be enabled for the field As in this pic

2. Then enter your desired timestapm(in your case: MM/DD/YYYY HH:MM:SS TT) 3. Ignore the warinigs and proceed further. You will be able to upload without any rejections.

Answer 3

Simply click on the timestamp dropdown and start typing your custom timestamp which in this case is "MM/DD/YYYY HH:MM:SS TT"

Answer 4

I did it with pandas: First I have opened Jupyter-Notebook, and read the csv:

    df = pd.read_csv("Chicago_Crime_Data-v2.csv") 

Then I used this function:

    df['DATE'] = pd.to_datetime(df.DATE)

And I save it back to the file:

    df.to_csv("Chicago_Crime_Data-v2.csv")

Now it would accept it. (Just see if there is more than one column with this problem)