SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Another help needed with Mysql date format

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by paul_paisley View Post
    I thought mysql could reverse the date as in changing 190810 to 2010-08-19.
    this is exactly what the STR_TO_DATE function is for

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is exactly what the STR_TO_DATE function is for

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

    Paul

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by paul_paisley View Post
    ... how can this function be utilized?
    do me a favour, go grab the syntax from the manual and post it here -- just the syntax, not the explanation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, that was the next step... how exactly are you importing it? with the LOAD DATA command?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks. I would try that out now.

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by paul_paisley View Post
    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
    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.

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Since your date is in the format 'ddmmyy' try

    Code:
    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 ).

  13. #13
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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

  15. #15
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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
    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

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ok, I'll say it again:

    change the str_to_date in your query to this:
    Code:
    str_to_date(handover_date, '%d%m%y')

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    this -- 'handover_date' -- is a string

    this -- handover_date -- is a column

    can you see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guido thanks very much. It worked. I remain grateful.

    cheers

    Paul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •