Impala first day of month. Impala Date to String Conversion.

Impala first day of month scandate) = sub. LocalNow()) Step2: Use this code to filter data = Table. Could not resolve table reference. month AND DAY(t. iibxawm4 于 2021-06-27 发布在 Hive. NANOSECONDS_ADD(TIMESTAMP date, INT nanoseconds), NANOSECONDS_ADD(TIMESTAMP date, BIGINT nanoseconds) SELECT yyyy_mm_dd, id, name, cumulative_metric1, cumulative_metric2 FROM mytable WHERE yyyy_mm_dd = last_day(yyyy_mm_dd) mytable has daily data from the start of the year. DATEADD("Day", -1, DATEADD("Month", 1, @[User::PreviousMonthFirst])) The concept of January 1st as the year’s first day originates from ancient Rome. temporal. Add one day to get first day of current month. message id sy-msgid type sy-msgty number sy-msgno. AddMonths(-1) to get the first of the previous month. 999Z") select w. Set the host Change start day of the week impala. proc sql; create table test as select ln,workout_trial_dt, my_cd from table where my_cd='c2';quit; sample output ln workout_trial_dt my_cd 111 02JAN2015 c2 I need the date to show as the first day of the month. Stack Overflow. You can also adjust a TIMESTAMP value by adding Instead try this, which means First Day of next Month -1 day: =dateadd("m",1,dateserial(year(Today),month(Today),1)). destinationusername, w. 359')) AS mm DD = 2015-03-01 = (TYPE: DATE) MONTH, MON, MM, RM: Month. PookPook PookPook. WW, W: Same day of the week as the first day of the month. This made sense, as March aligned with the spring equinox, a time symbolizing renewal and agricultural activity. How can I select the last day of each month? Here's how this works: First we format the date in YYYYMMDD format truncating to keep just the 6 leftmost characters in order to keep just the YYYYMM portion, and then append '01' as the month - and voila! you have the first day of the current month. 000Z") and concat(to_date(now() - interval 1 days), "T23:59:59. 3 and higher, you have more flexibility to switch the positions of elements and use different separator characters. How to reformat multiple different dates in Impala/hive. Functions that format the date portion, such as date_add() or to_date(), typically return a string value. but I need to filter the records from first day of the previous month and last day of the previous month. Return type: DATE CURRENT_TIMESTAMP() Purpose: Alias for the NOW() Impala MONTHS_BETWEEN(DATE1, DATE2) the months in datediff are going to differ by 1 even on the subsequent days if you take let's say 31st of dec and 1st of jan - those 2 would give "1" in datediff both by year, month and day. Since last day of the month may vary but first day of the month will always be '01'. lastDayOfMonth() Select all dates between first day of month and current date. October 1st, you drove it. * FROM tablename t INNER JOIN (SELECT MONTH(scandate) AS month, MAX(DAY(scandate)) AS day, cluster FROM tablename t GROUP BY MONTH(scandate), cluster) sub ON (MONTH(t. I know Today() stands for Today's date. Impala - First date of Month from string date. 000000001 s 1/10^9 s 此外还有一些时间单元季度 qu. DATEADD(DAY, 1, ): Adds one day to get the first day of the current month. ) HH, HH12, HH24: Hour. js: 2016-09-01 00:00. In Teradata, we can derive the first day of the month from a specified date. "1"; I need something similar to get 1st day of NEXT month It is referenced as the replacement for UTC_USEC_TO_DAY(t) in LegacySQL when used with a Day datepart. make start of week as monday or sunday influxdb. 1 wrote dates earlier than 1582-10-15 to a Parquet or Avro table, those dates would be read back incorrectly by Impala and vice versa. 4. with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. Table WHERE cast(ts/1000 as TIMESTAMP) BETWEEN "2021-01-05 00:00:00" AND "2021-01-05 23:59:59" GROUP BY unit ORDER BY unit; Is there any equivalent command in the IMPALA so that I can get the first and last value of the each column as am CURRENT_DATE() Purpose: Returns the current date. StartOfMonth(DateTime. 2. However not all months starts with 01, some starts with 02, 03, 04 etc. ) month, day, and so on in the date string. SELECT DATE_TRUNC('2021-05-20', year); Result: 2021-01-01. Querying across months and days. ; The formula returns the first day of the last month for the date in cell B5. ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days) The most recent date that is the same day of the week as the first day of the month 'DDD' 'DD' 'J' Yes : Yes : Day 'DAY' 'DY' 'D' Yes : Yes : Starting day of the week (Monday This article organizes the time function commonly used in IMPALA, and the method of obtaining the current date and the last month's date to the string, the same manner, readers can also use the listed These time functions, combine any of the form you want, if you get the first day or last day. Impala query for yesterdays data without typing date possibly using python. query for first and last day of month. SelectRows(#"Changed Type1", each [Date] < [Custom]) Result: Of course, you could remove Custom column later. hive sql:extract last 12 months rows. adddays(-1) – ukgav Commented Dec 18, 2019 at 12:47 How do I get the first and last day and time of the current month in the following format in moment. day AND t. dateadd(m, datediff(m is a technique for working out the first day of month for any date +v. isEqual(someDate. if sy-subrc 0. MI: Minute. write: 'first day', date1, 'last day' , date2. How to Target Last Month in SQL Query. Datename(dw, Dateadd(dd, -Datepart(dd, Getdate()) + 1, Getdate())) If the Datename is Saturday the add 2 days to the first day of the month. The answer depends on how you define # of months between two timestamps. sql; sql-server; Share. month and day. e_last_day = date2. This representation introduces an interoperability issue between Impala and older versions of Hive: month, and day of the DATE is returned along with the time of day component set to 00:00:00. This article organizes the time function commonly used in IMPALA, and the method of obtaining the current date and the last month's date to the string, the same manner, readers can also Get first day of the given timstamp using Impala SQL. The function MONTHS_BETWEEN returns the number of months between the dates specified. The DATE logical type annotates an INT32 that stores the number of days from the Unix epoch, January 1, 1970. SET:FIRST_OF_MONTH = FIRST_DAY(CURRENT_DATE) The host variable FIRST_OF_MONTH is set with the value representing the beginning of the current month. execute("SELECT sourceaddress,count(sourceaddress) as count FROM table GROUP BY sourceaddress ORDER BY count desc LIMIT 10") Even if I can define yesterday in python before the query like: yesterday = str((pd. There are several ways to find out the first day of a month. Using cmd batch file can i get first day of month and run an action? For example every first day of the month shutdown the pc. Maximo UI SQL Select Month. Hence they may not have added that function. 1. exceptions. migrating from mysql i needed the first_day and last_day functions. For example, 2019-08-09 is Friday. Schedule task on the first workday of the month. This would be: select DATEADD(DD,1,EOMONTH(Getdate(),-2)) firstdayoflastmonth select DATEADD(DD, 1,EOMONTH(Getdate(), -1)) firstdayofthismonth I came up with this to satisfy the query: devicereceipttime BETWEEN concat(to_date(now() - interval 7 days), "T00:00:00. Ask Question Asked 6 years, 6 months ago. Queries Used: First day of the current month select to_char(trunc(sysdate,'MM'),'DAY') from dual; The above query is working fine The underlying Impala data type for date and time data is TIMESTAMP, which has both a date and a time portion. (Not necessarily the current day. Pou Thapioume Pou Thapioume. Dynamic SQL query which is based on current month. Presto: Last day of the month prior. How is this possible in Impala? I managed only to find the weekofyear(). 6. DDD, DD, J: Day. It relieves a developer's worries and eliminates the necessity for user-defined functions. ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days) The most recent date that is the same day of the week as the first day of the month 'DDD' 'DD' 'J' Yes : Yes : Day 'DAY' 'DY' 'D' Yes : Yes : Starting day of the week (Monday Identify Months Between Two Dates – Apache Hive & Impala. However I need to get the date and time of the first and last day of the current month, any way to do this? e_first_day = date1. first day of Add a first day of the Month column and change the type of it to date =Date. Now, how can i get the first and last day of that months? changing the query. ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days) The most recent date that is the same day of the week as the first day of the month 'DDD' 'DD' 'J' Yes : Yes : Day 'DAY' 'DY' 'D' Yes : Yes : Starting day of the week (Monday Same as MONTHS_ADD(). ; The formula returns the first day of each previous month for the dates in the Date. In the same lines can someone please tell me how do I get the first day of current month (example 1/1/2017 currently) and first day of Let’s assume your application receives a datetime object, and you want to convert the given date to the first day of the month. PookPook. Second test if current date day > 25 and get a true/false on that. This function can also be used to get the first day of a quarter or a year, etc. 467 3 3 gold badges 11 This means you can get the first day of the current month with a formula like this: =EOMONTH(A1,-1)+1 This formula "rolls back" a date in A1 to the last of the previous month, then adds 1. which means current month feb, but I need to get records only between 1 Jan and 31 jan. with(lastDayOfMonth()) This solution uses TemporalAdjusters utility from java. "-" . So either you can follow that approach in the blog or you can simply replace the day '01'. select first day of month in google sheets query. The EOMONTH function returns the last day of the month, and you can use it with an offset to get the first day of the month. also check. If date is TIMESTAMP, returns TIMESTAMP. If I use 0, EOMONTH will give me the last day of the given month, and if I use 1, it will give me the last day of the next month. Ilana Junius has accused her former friend, Carla Walls, of refusing to pay for the 2004 Saturn View she purchased. Worth noting that inside the for all other/next month it should return 01st day of corresponding month. DateDiff returns an integer. SELECT unit, FIRST(Temp) as Start_Day_Value, LAST(Temp) as End_Day_Value FROM Sensor_Data. Yes ma'am. 0001 s 1/10^3 s微妙 Microsecond （μs） 0. Range: 0001-01-01 to 9999-12-31. last day of month in impala. Viewed 2k times how to change the first day of the week in PostgreSQL. BuddhistCalendar忽略第三个，只看前两个实现类，有这么一段代码：case DAY_OF_MONTH: // synonym of D If you want to get entire previous month, it's usually better to go >= [first day of previous month] and < [first day of this month], which will include all dates & times of the previous month. The following code snippet shows the month start date of the current month: SELECT CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1 AS MONTH_START_DATE; Output: MONTH_START_DATE. SELECT CAST(CONVERT(VARCHAR(6),GETDATE(),112) +'01' AS DATETIME) AS StartOfMonth Query Impala cursor. If I were to solve this problem using Impala built-ins, I'd first sort on the timestamp column, then convert the timestamp to a date string with TO_DATE(ts), then use Same as MONTHS_ADD(). Get last day of the given month using Impala SQL. 000001 s 1/10^6 s纳秒 Nanosecond （ns） 0. cluster = sub. month, and day of the DATE is returned along with the time of day component I've seen all the previous discussions about finding the first day of the month using sysdate but I'm trying to get the same first day of the month from the user input but not able to do so. input_false = 1. First, common time function 我有一个时间戳格式的变量名date。我试图获取该月的第一天。例如，如果日期是2022-02-27，则我需要的输出是2022-02-01。 I have a DataFrame with DateTimeIndex, for 10 years, day by day. ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days) The most recent date that is the same day of the week as the first day of the month 'DDD' 'DD' 'J' Yes : Yes : Day 'DAY' 'DY' 'D' Yes : Yes : Starting day of the week (Monday 基于impala 3. The below steps show how to use the datetime. JapaneseImperialCalendarjava. endif. date filtering of first day of the previous month and last day of the previous month impala. SQL SELECT First Day and Last Day of the Month. GregorianCalendarsun. Built-in functions aid analysts in the process of analyzing, cleansing, transforming, and modeling data in order to identify valuable information, draw conclusions, WW, W: Same day of the week as the first day of the month. Thanks in advance! date; cmd; Share. Enter a month and get the 1st and last day for it in the particular year. , the first day of the month or the start date of a month. 2 版本时间的基本单元 time_unityear 年month 月week 周day 日hour 小时minute 分钟 秒 second毫秒 Millisecond （us） 0. Hello All. The default is 7, Sunday. with(firstDayOfMonth())) last day of month: someDate. e. Impala Date to String Conversion. others = 4. 关注(0) | 答案(2) | 浏览(321) 我在employee表中有一个data\u date列（字符串数据类型），其值为yyyymmdd格式。 I want to print in impala that 2020-03-01 is in the first week of March. The second parameter (the 0) represents the 0 date in SQL Server, which is Jan 1, 1900. t009_notfound = 2. In the example shown, months is supplied as -2, which causes EOMONTH to return 4/30/2015. Was the first day that you said you drove the car? Yes, ma'am. Thanks! hive; hiveql; impala; Impala - First date of Month from string date. About; last day of month in impala. Share. In Impala 1. Modified 5 years, 7 months ago. Step 2: Drag the fill handle of cell C5 downward to apply the formula to the rest of the cells. Impala 日期时间 如何在Impala中获得上个月的最后一天？ - 我希望将Impala上个月的最后一天作为任何类型(最好是字符串)。 它应该是很好的可读性和有效性。 '01' ), /* Get the first day of this month: YYYY-mm-01 */ 1 ) /* Subtract one day */ AS DAY 一个班轮 SELECT days_sub(regexp_replace(regexp_extract(cast Convert first field to timestamp like below - to_timestamp(date_part, 'yyyyMMdd') date filtering of first day of the previous month and last day of the previous month impala. Getting the last day of last month does basically the same thing except it adds one to number of days in the month and subtracts that value from DateTime. Use code FABINSIDER for a $400 From there it uses that DateTime and uses . (TimeStamp / Date Date) - the first few days Dayofmonth, Day (TimeStamp / Date Date) - a month of the first few days Dayofweek (TimeStamp / Date Date) - the first few days Weekofyear (TimeStamp Date) - a few weeks DayName Press ENTER. But given the last day, you can add a day and subtract a month to get the first day. 0. Functions that extract a single field, such as hour() or minute(), typically return an integer value. 4 and higher. Find sum of Current month and Previous month values. 2020-01-02 2020-01-03 2020-01-31 2020-02-03 2020-02-29 2020-03-02 Expected df must be: 2020-01-02 2020-02-03 2020-03-02 There are plenty of built-in functions available in Apache Impala that helps in analyzing the data and business logic implementations. The DATE type is supported for HBase, Text, Avro, and Parquet. here they are: create or alter function first_day(d timestamp) returns date as begin return cast(d - extract(day from d) + 1 as date); end and. That was the first day. 990 6 6 Explanation - You first create a range of numbers. Best Regards, Lin Configure sp_add_schedule to run on the first day of every X months/weeks. 5 use the relativedate function. For example, the datetime is ‘2022-9-14‘ and you want to write a code that will return you ‘2022-9-1‘, i. It is common practice to use it as import static but you can also use it as TemporalAdjusters. The date field in the table that the formula is looking at only has one row per date Solved: HI all, I need to get the 1st day of last month (based on the current calendar month) into a measure but I have hit some confusion. 简单来说，没区别。Calendar 类的 add 方法是个抽象方法，在 JDK 中有三个类实现了这个方法，分别是：java. destinationhostname,w. If date is If date is the last day of a month, the return date will fall on the last day of the target month, e. In the output of the above, I only see the last date for January but not February. Same as months_add (). Available in Use the DATE data type to store date values. This representation introduces an interoperability issue between Impala and older versions of Hive: If Hive versions lower than 3. Timedelta(days=1)). MMV MMV. Real Example: if --> Years = 1 and Months = 12 1) Convert both to days to compare them: Years = 365 days; Months = 365 days After conversion : (Years = Months) Returns TRUE. Impala Dates for impala-find first day on month from字符串值 . Then subtract a day and so that's the formula I use for PreviousMonthLast. Carla claims the contract she had with Alana was for a different vehicle. Use DATETIME_TRUNC function SELECT DATE_TRUNC('2021-05-20', month); Result: 2021-05-01. DAY, DY, D: Starting day of the week. So far I've got the code to insert a new row in row 5, I've attempted to change the date to the 1st of the month (with an 'invalid procedure or call' error), and I've got an select date_sub(last_tran, first_tran) as date_len , user from (select max(id_date) as last_tran ,min(id_date) as first_tran ,user from table_1 a join table_2 d on a. I want to do Month to date and year to date calculation without using MTD and YTD functions. Now add one day If date is the last day of a month, the return date will fall on the last day of the target month, e. Impala supports the following date and time functions: Purpose: Adds months to date and returns the new date value. cluster) – The 2nd argument is months, which specifies how many months in the future or past to move before returning the last day. replace() method to find the first day of I need to get the first date of month of any date using Spark SQL. I need to extract rows which corresponds to first day of each month. Improve this answer. Join us at the 2025 Microsoft Fabric Community Conference. How to get the First day and last day of the month from given month and year in sql. A TIMESTAMP value truncated to the hour is always represented in 24-hour notation, even for the HH12 argument string. AddDays, giving you the last day of the previous month. But in Impala documentation,I could not find any function how to extract it. If True then add add one month to the start of month datetime object. SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS FirstDayOfMonth; EOMONTH(GETDATE(), -1): Gets the last day of the previous month. Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Then you can pick your date range less than date2. TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', WEEK, 'UTC') Here is the page for migrating from Legacy to Standard sql Keep the first day of the month, even when it is repeated in R. If the current day is 2000-02-10, then FIRST_OF_MONTH is set to 2000-02-01. Powershell - Get the first and last day of the previous month. util. 3. I started. Improve this question. Available in Impala 1. create or alter function last_day(d timestamp) returns date as begin return dateadd(1 month to first_day(d)) - 1; end The underlying Impala data type for date and time data is TIMESTAMP, which has both a date and a time portion. I have date column in my table. @mydate = 10/8/2019, subtract day component (8 days) will give us 9/30/2019. Impala SQL - How to Truncate Timestamp to Day? 1. Ex: if i select Month OCT and Year 2021 then Startinvoice same with startbilldate and endinvoice get lastday of month from startinvoice but when i select Month NOV and Year 2021 then Startinvoice = 01 nov 2021 and endinvoice = 30 nov 2021 next month it should return 01st first day of month: someDate. How . All calls to CURRENT_DATE() within the same query return the same value, and the value does not depend on how long the query takes. Skip to main content. The result is the first day of the "current" month (i. Is there any way to change this behavior to give me a Sun - Sat week? I need to change it for my query only, changing a server or cluster wide setting is not an option. PowerShell scheduled task. I am currently using the CALENDAR function to start my DateDim table. Now. call function 'first_day_in_period_get' exporting CData Cmdlets for Apache Impala - Update - DATE Functions: Cmdlets for Apache Impala The optional integer representing the first day of the week. Powershell | Only run if SAP HANA: Get the First Date and the Last Date of Month from a Given Date | WuaWua. The problem is when we have years = 10 (for example), we must take in account the fact that at least two of them have 366 days. . _impala 日期函数 . id= d. Follow asked Oct 23, 2014 at 0:47. There is LAST_DAY in HANA but not FIRST_DAY. time. Code snippet. If the first expression is later than the second, the outcome is positive, How to get last day of previous month in Impala? 0. How can I get around this? Instead of manually putting in the amount for each first day of the month, is there a way to write an excel if statement so that it will be a certain value for the first day of a month and zero on all other days? Sort of like: =IF("day is first day", $100,$0) That way I can drag the formula all the way down. 在Impala中，要获取上月第一天的最早时间，精确到时分秒，可以使用Impala提供的日期和时间函数来完成。 这里有几个步骤可以帮助你计算出这个时间： 获取上月第一天的 impala 采用的纪元和unix-like 系统的纪元一样是从1970-01-01 00：00:00 开始计时的。 这个是 unix_time 相关的重要的概念。 impala中常见的时间单元 第一个方法-将时间戳列中的月份部分截断为月份的第一天。 第二种方法--从timestamp列中获取yyyyMM，然后将01添加到该字符串中以获取第一天字符串，然后将其转 Impala supports the following data and time functions: Purpose: Returns the specified date and time plus some number of months. The early Roman calendar, attributed to Romulus, initially consisted of just ten months, with March (Martius) as the first month. For compatibility when porting code with vendor extensions. You can also adjust a TIMESTAMP value by adding Unfortunately there's rarely an elegant answer to this question, even in full-featured programming languages like Python. By traveling back 2 months, then adding one day, we can calculate the first day of the previous month from any given date. Find out last month's data Let's understand the method to get first day of current month, we can fetch day component from the date (e. The net effect is removing the day (and time) portion of a datetime value. About; Here is a great way to do convert date/timstamp to first day of the month > to_date(date_trunc('MM', '2015-03-05T09:32:05. if it is sunday then add 1 day to the first day of the month else get the first day of the month How to get last day of previous month in Impala? 1. It is currently looking at the dates in my main table called 'TechDev DB'. If'' it's a month and year then just construct a date by concatenating 01 and then convert the string to a date using todate() function. And then add it to the date1 to get a range. So, the datediff calculates the integer number of months since Jan 1, 1900, then adds that number of months to Jan 1, 1900. As title says, I'm stuck on finding a way to get the first and last date of the current month with JavaScript or jQuery, and format it as: For example, for November it should be : var firstdate = The problem is the way the date is formatted as it's dd/mm/yyyy which means if the day doesn't match the previous row, then it won't delete, even if the month is the same. Convert Timestamp to YYYYMMDD format in Impala SQL. date()) and somehow incorporate it into an Impala query. Follow edited Apr 1, 2013 at 11:34. E. Result: 5 DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision) Yes, first set a datetime to the start of the current month. , '2020-09-29' to '2020-09-01'), so I had originally used: date_format(LOG_DATE,'yyyy-MM-01') as FIRST_DAY_MONTH. The common time units in Impala are: Year, Month, Week, Day, Hour, Minute, Second, Millisecond, Microsecond, Nanosecond. select last day of each month. Literals and expressions: The DATE literals I have impala query to select records from table. skip to main content. Why is there an issue with the ADD_MONTHS function in Oracle SQL. asked Apr 1, 2013 at 11:29. MONTHS_SUB(DATE'2019-02-28', 1) returns DATE'2019-01-31'. 1 1/02/2021 Set the host variable FIRST_OF_MONTH with the first day of the current month. Okay. Any references to the CURRENT_DATE() function are evaluated at the start of a query. g. How to get (year of week number, week number) for a date set. Convert timestamp to date format using Imapal SQL. Same as MONTHS_ADD(). I can get the current date and time like this: moment(). In this I need to change a date column to the first day of the month (e. where you can give the second argument as the ((day number of your date)*-1 +1). It also accepts Week and Month as a parameter which may meet your requirements. to_datetime('today') - pd. I have this to get 1st day of current month $_SERVER['dataInicio'] = date('Y') . SELECT LAST_DAY('2010-06-15'); Will return: 2010-06-30 Unfortunately, MySQL does not provide any FIRST_DAY function to retrieve the first day of a month (not sure why). how to find first day of the next month and remain days to this date with php. @mydate) using DATEPART and subtract this day component to get last day of previous month. Add a month to the first of the month resulting in Feb 1. First Day of Spring 2025---Why weren't Sunrise and Sunset Exactly Twelve Hours Apart? What, if any, is the signficance of how Romans 10:17 is worded? You can use the LAST_DAY function provided by MySQL to retrieve the last day of any month, that's easy:. Impala Dates for Weeks, Months, Quarters, & Years. The same with Months - we have 30 and 31 The above EOMONTH formula uses two arguments: Start Date: the date from which Excel uses the month value and calculates the first day of the month; Months: The number of months before or after the start date. id group by 3) time group by 1,2 It seems that the date_sub functions 2nd argument has to be an integer representing days. Request you to help me out. should i give the table name as mentioned below,SELECT t. destinationprocessname, count(*) as count \ from winworkstations_realtime w \ where First day of a month. format('YYYY-MM-DD h:m') which will output in the above format. t009b_notfound = 3. 7. Follow answered Feb 21, 2020 at 10:55. date('m') . We can also use the same formula with the TODAY() function to find out the first day of the month Then check the day of the previous date by using Datename function. Use DATE_ADD or DATE_SUB function I only want to return the first day of each month using the CALENDAR function ‎07-13-2018 09:24 AM. While I could code in the rules for days of month, I'm going to let the libraries do the work. number is used to increase it by one month each time; 0 and datediff(m, @d1, @d2) this condition gives us all the numbers we need to generate a first-of-month date for each month between @d1 and @d2, inclusive of both months I am using impala to find the starting day of the week, like this: select TRUNC('2018-01-01', "D") Which gives the start day based on a Monday - Sunday week. qfkj fen dpjpa awswd twqsqk yvtgc glqa uxkbm auqfeu hnkzw iinfxpl iupzkxq lgnaj hcqwbrl jpijcfd