Converting "Apr" to "04" in date field

["Dela Cruz, Vergel" <Ve...@ap.jll.com>]

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.

Re: Converting "Apr" to "04" in date field

[Charles Givre <cg...@gmail.com>]

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 <Ve...@ap.jll.com> 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.