I am using Hive (which is similar to SQL, but the syntax can be little different for the SQL users). I have looked at the other stackoverflow, but they seems to be in the SQL with different syntax.
I am trying to the get the first day of the month through this query. This one gives me today's day. For example, if today is 2015-04-30, then result would be 2015-04-01. Thanks!
FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') as first_day_of_month_transaction
--DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as first_day_of_month_transaction --SQL format. Not compatible in Hive.
To get the first day of the month, you can use:
date_add(<date>, 1 - day(<date>) )
Applied to your expression:
date_add(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd'), 1 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd')) )
But this will work for any column in the right format.