How to convert date format "dd/mm/yy" to "yy/mm/dd" for mysql database inserting?

John

When I insert a date from a file that has it formatted "dd/mm/yy" into my database table with the date formatted "yy/mm/dd" the date is wrong:

Instead of getting 2019:04:11 I get 2011:04:19.

I want to keep the database format ("yy/mm/dd")

I have tried:

actualdate = DATE_FORMAT(j[0], '%y-%m-%d')

cursor.execute(actualdate)

but it tells me error: name 'DATE_FORMAT' is not defined

import mysql.connector
sql = mysql.connector.connect(host='',user='',password='',db='')

cursor = sql.cursor()
f = open("C:\Cumulus\data\Apr19log.txt","r")

st=[i.strip().split(',') for i in f.readlines()]

actualdate = DATE_FORMAT(j[0], '%y-%m-%d')  
cursor.execute(actualdate)

sqllist = "INSERT INTO station_fenelon (variable, date, time,             
outside_temp, outside_humidity) VALUES (%s, %s, %s, %s, %s)"
record = [(i+1, j[0], j[1], j[2], j[3]) for i, j in enumerate(st)]
cursor.executemany(sqllist, record)
sql.commit()

error: name 'DATE_FORMAT' is not defined

George Pant

DATE_FORMAT() is a MySQL function and you call it directly in your python script so you get an error message that is not defined.You should remove this line.

You could use STR_TO_DATE to convert your string to a date

sqllist = "INSERT INTO station_fenelon (variable, date, time,             
outside_temp, outside_humidity) VALUES (%s, STR_TO_DATE(%s,'%d/%m/%Y'), %s, %s, %s)"

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Error while inserting date to Mysql database

How to get the date column from MySQL database with prepared statement and format it

puzzled with how to convert ddmmyy string to dd-MMM-yy date

How to conver date on arguments into format date yyMMdd

How to convert a sequence of characters into a date format to store in the database?

PHP date() format when inserting into datetime in MySQL

Error converting string date format 'yymmdd' to datetime

How to convert a string date in european format to a valid mysql date format?

How to neglect timestamps when inserting date to MySQL database

Convert date format to mysql timestamp

Convert double to mysql date format

How to convert date to mysql date format

How to convert a string to date in mysql inserting from a txt parser? - JAVA

How to change the date format from YYMMDD to YYYY-MM-DD in java?

How to convert GET parameter from custom date format to database date format?

How to convert user input into MySQL date format?

Convert date in string to date object for inserting in database

how to convert date format in a textarea php mysql

Convert Date String(yymmdd) to Date Object in JS

Convert VARCHAR in format YYMMDD to YYYYMMDD and ignore invalid date formats

How to convert CRSP date type into normal date type YYMMDD?

How to send correct date format from dateTimePicker to MySQL database?

How to convert a date/time to MySQL date/time format

How to convert date format in MySQL?

How do you convert date format to yyMMdd in cell click event?

How to convert string of format ddmmyyyy to yymmdd

How to convert a date formatted (YYMMDD) as string but with 00 days?

convert date into mysql datetime format

How do I change a character value in the format YYMMDD to a date format in PROC FEDSQL

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive