Hi Vergel, I think you’re looking for the TO_CHAR() function (https://drill.apache.org/docs/data-type-conversion/#to_char <https://drill.apache.org/docs/data-type-conversion/#to_char>) This function takes an expression and a format string, so you should be able to perform the conversion with it. V/R, — C
> On Apr 9, 2017, at 21:54, Dela Cruz, Vergel <[email protected]> > wrote: > > Hi, > > I have a json file that contains a date field in " Tue Apr 04 02:11:35 +0000 > 2017" format. I was able to use substr and concat to convert the date to " > 2017-Apr-04 02:11:35" but having problems converting "Apr" to 04. > Is there a way to do this ? > > I have a attached my file. Here is my query. > > > SELECT > CONCAT(SUBSTR(t.created_at,27,4),'-',SUBSTR(t.created_at,5,3),'-',SUBSTR(t.created_at,9,2),' > ',SUBSTR(t.created_at,11,9)) > , > t.created_at,cast(t.`user`.name as varchar) as tweeted_by, > cast(t.`user`.`followers_count` as int) as tweeted_by_followers_count, > cast(t.`user`.`favourites_count`as int) as tweeted_by_favourites_count, > cast(t.`user`.`friends_count`as int) as tweeted_by_friends_count, > cast(t.text as varchar) as text, cast(t.favorite_count as int) as > favorite_count, > cast(t.retweet_count as int) as retweet_count FROM > `apvergeldelacruz`.`default`.`./User_Storage/Twitter/stream*` t > > Thanks, > Vergel > > > This email is for the use of the intended recipient(s) only. If you have > received this email in error, please notify the sender immediately and then > delete it. If you are not the intended recipient, you must not keep, use, > disclose, copy or distribute this email without the author's prior > permission. We have taken precautions to minimize the risk of transmitting > software viruses, but we advise you to carry out your own virus checks on any > attachment to this message. We cannot accept liability for any loss or damage > caused by software viruses. The information contained in this communication > may be confidential and may be subject to the attorney-client privilege. If > you are the intended recipient and you do not wish to receive similar > electronic messages from us in the future then please respond to the sender > to this effect.
