1

i want to display the first day of the month for an existing column, for example 12/16/2018 i want this to display as 12/01/2018 in Google Big_Query

Anil
  • 65
  • 5
  • Please read [How to Ask](http://stackoverflow.com/help/how-to-ask) so your next question(s) will show what you actually tried. Also please read how to show a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) of the code that you are having problems with – Mikhail Berlyant May 17 '18 at 22:22

1 Answers1

2

For BigQuery Standard SQL 

SELECT FORMAT_DATE('%m/%d/%Y', DATE_TRUNC(PARSE_DATE('%m/%d/%Y', '12/16/2018'), MONTH))
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • What ever happened to asking to see what users have attempted first? ;-) – Graham Polley May 17 '18 at 22:12
  • agree. i kept this rule for quite some time, but then gave up as someone anyway answered without asking :o( – Mikhail Berlyant May 17 '18 at 22:14
  • meantime, I mostly gave up with this rule for new users or those with quite low reputation score, so they can see value of SO in terms of getting answers so they most likely will come back with new questions. While for those with high reputation score - I still avoid policing and rather provide answer - but most likely downvoting the question because/if they lack any own attempts. this might be good or not approach - but that's what I ended up as a result of my daily presence here on SO last 3 years (I think i missed just few days during that time) I found this the most optimal way for me – Mikhail Berlyant May 17 '18 at 22:31
  • 1
    @GrahamPolley - this is great example of what I meant above - https://stackoverflow.com/q/50686475/5221944 :o) – Mikhail Berlyant Jun 04 '18 at 18:55