Another help needed with Mysql date format

Databases
1

Hi everyone,

I have searched the forum and reviewed responses to questions related to mysql date format. However, none of these responses seem to solve my problem and therefore need put it forward hoping that someone could help.

I have set up my mysql database and populating the tables using import command to import data from text files. The date from the text file is in the form “190810” i.e d.m.y. After populating my tables I realised that the mysql is truncating the date entry and producing extraordinary dates upto year 2013, 2021 and so on in in its default format. I thought mysql could reverse the date as in changing 190810 to 2010-08-19.

I am aware if you are retrieving data you can display the date as you want it. I am not retrieving data in this case but storing data from a text file which has date that must not be truncated.

So how do I import this data from the text file and maintaining the correctness of the date even if it is in mysql default format. Is there no other way I can achieve this. I am completely run out of ideas. Please advise.
Many thanks

Paul

2

Thanks for the reply and i wont start new threads on the same issue. The suggestions i have got are not working. please can people who are experts in mysql and php tell me something that is workable? I cant figure this out myself.

i have massive data in textfiles with dates in dmy format, i need to import it into mysql while preserving the correctness of the date. I am out of ideas please help.

3

Somebody please help!!!
One person has prosposed the use of str_date_function and creating a duplicate table and then inserting. It is not working though.

I am not retrieving data from my database but storing from text files. How can I preserve the correctness of the date while storing it in mysql date default format. The date is getting truncated. please assist my good friends.
The format I have from the text file am importing from is this 070610, 070610. I want to preserve this correctness. It could be 2010-06-07 which is ok in mysql. Thanks in advance.

4

this is exactly what the STR_TO_DATE function is for

:slight_smile:

5

this – ‘handover_date’ – is a string

this – handover_date – is a column

can you see the difference?

6

Ok, I’ll say it again:

change the str_to_date in your query to this:


str_to_date(handover_date, [B][COLOR="Red"]'%d%m%y'[/COLOR][/B])
7

Thanks Guido,

I ran the following query:

insert into handover2(time, date) SELECT time, str_to_date(‘handover_date’, ‘%y,%m,%d’) FROM handover;

and got this output: 0000.00.00

it is not changing the format and that is where i am hooked now no progress. I appreciate your assistance

Paul

8

Guido thanks very much. It worked. I remain grateful.

cheers

Paul

9

I just gave you another suggestion. Did you try it? What is the result? And don’t say: it didn’t work. Explain what the exact result is.
And by the way, someone more expert in MySQL than r937 will be hard to find :slight_smile:

10

Since your date is in the format ‘ddmmyy’ try

str_to_date(yourdatecolumn, '%d%m%y')

And don’t start a new thread with the same question!

By the way, don’t call your columns date and time, these are reserved words in MySQL. Of course, you might put backticks around them, but that’s kind of annoying (IMO :slight_smile: ).

11

please check for me what am doing wrong below is my insert stm and is entering date as 0000-00-00 . i used the following insert statement.

insert into handover2(time, date) SELECT time, str_to_date(‘date’, ‘%y,%m,%d’) FROM handover; thanks

12

Many thanks. I would try that out now.

13

okay, import your data into a holding table where the date is a simple varchar

then do an INSERT SELECT to copy the data into your target table from the holding table, using the STR_TO_DATE function in the SELECT part

14

am using phpmyadmin to import the files as csv though it can still import as txt. and below is the format of the data am importing. i.e time and date separated by commas

193138.000,070610,
182605.000,190810,
051953.000,200810,
174651.000,210810,
140341.000,120610,
150441.000,130610,
131631.000,100610,
125717.000,110610,
144634.000,080610,
182417.000,050710,
142139.000,070710,
104605.000,030710,
064022.000,300610,
180049.000,010710,
193743.000,270610,
142900.000,290610,
100546.000,250610,
135640.000,020710,
130932.000,260610,
124834.000,220610,
222008.000,230610,
084930.000,200610,
134543.000,210610,
130851.000,180610,
181812.000,190610,
163119.000,140610,
201826.000,150610,
142904.000,090610,
072241.000,210810,
174651.000,210810,

15

okay, that was the next step… how exactly are you importing it? with the LOAD DATA command?

16

Hi there,

STR_TO_DATE(str,format)

SELECT STR_TO_DATE(‘01,5,2013’,‘%d,%m,%Y’);

The above the syntax. Please remember am importing date data from a text file into mysql and not retrieving.

Thanks.

17

do me a favour, go grab the syntax from the manual and post it here – just the syntax, not the explanation

18

Thanks for the response. Since am populating my tables via import from text files, how can this function be utilized?

Paul