MySql str_date with JSON object

Question

I have following query

 SELECT  str_to_date(`created_at`,'%Y-%m-%d %H:%i:%s') AS `created_at 
 FROM `categories`

If I run this query on PHPMyadmin it's working fine. and gives result

2014-09-15 11:59:55  ,  2014-09-15 12:40:12

I am getting Result in JSON Format OutPut is showing like this

2014-09-15T06:29:55.000Z
Mon Sep 15 2014 11:59:55 GMT+0530 (India Standard Time)

I want same output which is in MYSQL. I have datetime in MYSQL. Does anybody help me out

You are probably fetching the value and sending it as a date object. Send it as a string object instead. — ngrashia
– ngrashia, Commented Sep 15, 2014 at 8:46

Regent · Accepted Answer · 2014-09-15 09:16:38Z

2

Try this one:

SELECT DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i:%s') AS `created_at` 
FROM `categories`

DATE_FORMAT gives you date as a string.

edited Sep 15, 2014 at 9:16

Regent

5,1743 gold badges23 silver badges36 bronze badges

answered Sep 15, 2014 at 8:59

Hitu

2861 gold badge2 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bala · Accepted Answer · 2014-09-15 08:56:25Z

2

Try this

SELECT  DATE_FORMAT(`created_at`,'%Y-%m-%d %H:%i:%s') AS `created_at 
 FROM `categories`

answered Sep 15, 2014 at 8:56

Bala

11.3k19 gold badges75 silver badges134 bronze badges

1 Comment

ngrashia Over a year ago

Please refer comment on the post by Hitu

ngrashia · Accepted Answer · 2014-09-15 08:54:57Z

1

STR_TO_DATE converts the string to a date object.

When the date object is returned via JSON, the result is returned as you have specified.

To avoid this do either of the below,

Convert the Date again to String using DATE_FORMAT and send this string value to your JSON Output.
Since your records are already in the string format that you want, just take the value and pass the string to your json output

Example: Approach 1

SELECT DATE_FORMAT( STR_TO_DATE('18,05,2009','%d,%m,%Y') , '%Y-%m%d %H:%i%s');

Refer fiddle here: http://sqlfiddle.com/#!2/3285c/3

edited Sep 15, 2014 at 8:54

answered Sep 15, 2014 at 8:49

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySql str_date with JSON object

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related