Mysql Date Format convert

Question

I want to sub string date from [09/Jul/2014:16:12:58 +0530] and want to feed it to Date type Field. My complete query is like this

    insert into SquidLog 
   (LocalIP,AccessDateTime,Url,HttpAccessCode,BytesAccessed,DestinationIP,
   SquidErrorCode,SquidTimeStamp,AccessDate,AccessTime) select LocalIP,
   AccessDateTime,Url,HttpAccessCode,BytesAccessed,DestinationIP,
   SquidErrorCode,SquidTimeStamp, 
   DATE_FORMAT(replace(SUBSTR(AccessDateTime,2,11),"/","-"),'%Y-%b-%d'), 
   SUBSTR(AccessDateTime,14,8) from SquidLogTemp;

How it is possible.

Your code suggests you want to accept input in several formats, is that really the case? — Álvaro González, Jul 26 '14 at 09:20
The date has to go in different field and time has to go in different field. — user3879423, Jul 26 '14 at 09:29
"The date has to go in different field and time has to go in different field" any particular reason for this? it could be a nuisance — Paul Maxwell, Jul 26 '14 at 10:02

Microlang · Answer 1 · 2014-07-26T10:01:22.460

0

try this:

insert into SquidLog 
   (LocalIP,AccessDateTime,Url,HttpAccessCode,BytesAccessed,DestinationIP,
   SquidErrorCode,SquidTimeStamp,AccessDate,AccessTime) select LocalIP,
   AccessDateTime,Url,HttpAccessCode,BytesAccessed,DestinationIP,
   SquidErrorCode,SquidTimeStamp, 
   STR_TO_DATE(replace(SUBSTR(AccessDateTime,2,11),"/","-"),'%Y-%b-%d'), 
   SUBSTR(AccessDateTime,14,8) from SquidLogTemp;

edited Jul 26 '14 at 10:01

answered Jul 26 '14 at 09:26

Microlang

534
4
7

It seems your field isn't a Datetime Type use STR_TO_DATE to convert in string and then parse a substring you wont: – Microlang Jul 26 '14 at 10:00

score 0 · Accepted Answer · answered Jul 26 '14 at 10:20

date_format(str_to_date(SUBSTR(AccessDateTime,2,11),'%d/%b/%Y'),'%Y-%b-%d')

    CREATE TABLE SquidLogTemp
        (`AccessDateTime` varchar(26))
    ;

    INSERT INTO SquidLogTemp
        (`AccessDateTime`)
    VALUES
        ('09/Jul/2014:16:12:58 +0530')
    ;

**Query 1**:

    select
           date_format(str_to_date(AccessDateTime,'%d/%b/%Y'),'%Y-%b-%d')
         , substring(AccessDateTime,13,8)
         , str_to_date(substring(AccessDateTime,13,8),'%H:%i:%s') 
    from SquidLogTemp



**[Results][2]**:

    | DATE_FORMAT(STR_TO_DATE(ACCESSDATETIME,'%D/%B/%Y'),'%Y-%B-%D') | SUBSTRING(ACCESSDATETIME,13,8) | STR_TO_DATE(SUBSTRING(ACCESSDATETIME,13,8),'%H:%I:%S') |
    |----------------------------------------------------------------|--------------------------------|--------------------------------------------------------|
    |                                                    2014-Jul-09 |                       16:12:58 |                         January, 01 1970 16:12:58+0000 |

http://sqlfiddle.com/#!9/a24ce/1

I have added one new field and insert the Date there. I will select it from there with the str_to_date() function. — user3879423, Jul 28 '14 at 09:26

Mysql Date Format convert

2 Answers2