2 DAX Questions about DATEDIFF. I have a list of leads data. I have the following that. This can be in either days, hours, minutes, months, quarters, seconds, weeks or years. From(Date. Follow. Date1. Aging = -11. In this example, 0 is returned because the start date is a Saturday and the end date is a Monday. Durée état = (DATEDIFF('TF GRA 700'[Date/Heure],'TF GRA 700'[Date de fin],MINUTE)/1440)*24. The reason is that the unit you use when calculating is 'hours'. Change the type to DateTime. DaystoExpiry = DATEDIFF (today (),DISTINCT ('transactiondiscount' [Expiry_Date]),day)Get Help with Power BI; Power Query; DATEDIFF function with Filter; Reply. comW3Schools offers free online tutorials, references and exercises in all the major languages of the web. Viewed 2k times 2 I have a power query, with some results maybe a null value. Selected. I have figured a measure to get the DATEDIFF between the Earliest and Latest Dates within the same column, if the state is "Closed Complete", by RPA #. So for example the DateDiff output for "01. Example 1 Syntax Duration. Please try this formula: If (DataCardValue42. AddDays(StartDate,2) els. Hours(Duration. Let Power BI. Power BI checks If the Text contains. Our hope is that. The first option you advised is something I have already tried via Add Custom column in Power Query. Then go to the Add Columns tab, select Date, and press Subtract Days. OPEN tickets won't have a follow-up value, and in those cases a datediff between the timestamp and NOW() would be required to calculate time to the current date. Steps. AddColumn (#"Expanded Changed Type1", "DateDiff", each Duration. DATEDIFF accepts either. Dim TheDate As Date ' Declare variables. technically i should date and time right9,508 Views. First day of the week is Monday. Have a look at the link provided above and check out the huge offer of different Date-functions in Power Query. It will block the memory. I have a date diff column that added , but i'd like it to show up in my power query. It can be similar to DAX bellow. 2: Add Years to the start date, so you can then get the remaining months. 4: Add Months to the start date so you can get the remaining days. Days (#date (2022, 3, 4) - #date (2022, 2, 25)) Output 7 Learn more about: Duration. Minutes([Date2] - [Date1]) The above approach produces the result if value is as follow: For example: [Date1] = 5/16/2020 4:00:00 pm, [Date2] = 5/16/2020 4:45:00 pm The. The first piece of M code generates a table containing two columns, you can use for practice - just paste that into a new blank query. Help with IF with DateDiff syntax. RoundDown (DateDiff (StartDate. Step-3: Now order column ID first ascending. There is now a dateDifference () function added in Power Automate to avoid these complex expressions steps. You can write Power Query formulas. Imagine if we could write SQL. The Power BI measures are done in the modelling side of the program and this is essentially Power Pivot, not Power Query. For example, the following formula calculates the number of days between the "Order Date" and "Ship Date" columns: DAYS_DIFF = DATEDIFF ( [Order Date], [Ship. For example, I have a following product report, if the product status is Old, displaying a 50% discount; if the product status is New, displaying a 20% discount as below screenshots shown. Returns the seconds portion of duration. You can then use either Power Query or DAX to calculate the difference between the two dates. The syntax for DATEDIFF DAX function is: = DATEDIFF (Start Date, End Date, Interval) Start Date: The date you want to count the difference from. But the problem is that the resulting column is in the Duration data type. 1 = Table. A date in datetime format that represents the end date. Here is how I proceed: 1. In the Power Query world, there aren’t control structures (plural); there’s just one control structure (singular). ‘ NETWORKDAYS ’ is used to return the whole number of working days between two dates (inclusive). Lets say you want to calculate duration between Start & End column in any table, first create a query as advised above and name it networkdays. You can simply use a DATEDIFF formula - DATEDIFF (TODAY (),FIRSTDATE ('Table_Name' [Date_Column]),DAY) without an if function and use it in conditional formatting with following conditions: greater than -10000 and less than -30. In addition to other posts, you can also use the following formula to calculate the difference between two datetime values even though there are in inconsistencies in the database. And create a card visual to display the result. Use date diff if these are two dates. Topic Options. EVALUATE VAR StartDate = DATE ( 2019, 07, 01 ) VAR EndDate = DATE ( 2021, 12,. All you need to do is just copy and paste this code into a blank queWith this new value, you can now calculate the difference between the date of the current and previous row. Click on the modelling tab-> New column from the ribbon in power bi desktop. Run (DateDiff (Today (),DateValue (Gallery6. 0. g. Meaning there are 24 datetime values per date. Call it [Sales Date]. I want to find the date difference between the date fields. Get Help with Power BI; Desktop; Service; Report Server; Power Query; Mobile Apps; Developer; DAX Commands and Tips; Custom Visuals Development Discussion; Health and Life Sciences; Power BI Spanish Community; Translated Spanish Desktop; Power Platform Integration - Better Together! Power Platform Integrations;. TotalDays([EndDate]-[StartDate])/30 . DATEDIFF ( [ModificationDateTime]; PreviousDatetime; MINUTE )*-1. I am trying to create categories based on the amount of time difference between the two dayss ie. 43,752 Views. LocalNow functions, however, I'm new to. It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks, months, quarters or years. DATEDIFF is available in DAX but not in Power Query Editor. Value = "Day Shift", DateDiff (StartTime, EndTime, Hours), If ( EndTime < StartTime, DateDiff. 3 - rename query like fxHoursBetweenDates. Click Close and Apply. Hot Network QuestionsIn response to anilc59. ここの記事を見ていて自分もカスタム関数で DATEDIFを作ってみたくなった。どうせならバグ（といっていのかどうか未だに不明）にも対応させ. I tried it,but the results are not accurate and i need the difference in years. If you want to achieve DATEDIFF in Power Query Editor then use Duration. Kind regards. TG had new status date 6-2-2019 - B had the new status date 7-2-2019. For this example, we will use the employee’s table data to get the number of working days between the last date and date of joining column using the Duration. PowerAppsDay1. First, the logic: 1: Do a straightforward DateDiff for Years. Simply copy the M code from her blog. Reply. So you can either use DATEDIFF() in a calculated column, which results in numbers for every row; or in an iterator function like SUMX(), which potentially also aggregates the numbers, depending on the filter context. From([End]-[Start])) for my data. Description. DateDiff requires an interval like month, year, day, etc. 02-10-2016 09:13 AM. This is my query:-. 'Join Date')),LookUp (Progress_Table,Name=Chosen,'Day 1'),Chosen); Yes to your first question. In this video, we explained How to calculate difference between two dates in Power Query of Power BI. e:if its less than 24hours only its showing correctly if its more than one ay its considering only hours but not dates. 0. Date. The. 🔗 Links:🌐 Read the blog Need a Power BI Developer? fact is, it's basic arithmetic, which could be easily solved in ETL, Banco or Power Query. Helper IV. Using the DATEDIFF Function in Power BI. Filter ( YourDataTableName, DateDiff ( Now (), 'End Date', Days ) <=90 ) Please click Accept as solution if my post helped you solve your issue. The difference between 01/01/2019 01/01/2020 would be 12 months. Thank you! 07-05-2021 11:16 PM. then we might be able to come up with a solution. DateDiff( Date(2020,1,10) + Time(12,0,0), Date(2020,1,10) + Time(14,0,0) Hours ) If you want more guidance than this I would suggest you share a screenshot of your app's interface so I can replicate it. You can try to define the null value first in the if statement when creating the custom column, like this: = if [Date_Picked] = null or [Date_Req] = null then null else nWorkHours (WHStarts, WHEnds, [Date_Picked], if [Date_Req] <> null then [Date_Req] else null, Holidays)/WHDuration. However, you can create a measure instead. TechNet6 hours ago · 22m ago. First go to query editor> split columns :Split the date/time columns into 2 columns: date and time,as you see below: Then create a calculated column to get the weekday of the field "create at"：. I have a measure called Days elapsed that looks at the number of days that have passed. Measure = IF(MAX('Project-Activity'[PLANNED_START_DATE])<'Project. almost works. You just expand this column as you need. 11-01-2018 06:16 AM. The reason for the error-message is that you aim to compare Date with DateTime-values. STEP 1: Calculate the difference between the Order date and Ship date to know how long it took. If within the same year, you can use Date. Power Query; GCC, GCCH, DoD - Federal App Makers (FAM) Power Platform Integration - Better Together! Power Platform Integrations;. since i need it in hours. chiibosoil 293 • 2 yr. , NextDate, DATEDIFF("D", Date, NextDate) FROM ( SELECT ID, AccountNumber, Date, ( SELECT MIN(Date) FROM YourTable T2 WHERE T2. Power BI July 2022 Feature Summary. Click on any table field/column. Big thanks to both of you for taking time looking at my challenge. I have gotten so far; //fnWorkDays let func = (StartDate as date, WorkDays as number) => let WorkDays2 = (WorkDays*2)+7, StartDate = if Date. Duration. Topic Options. If the date presented is 2nd Jan 2019, then weeknum will return 1 as the result, and if date demanded is 30th Dec 2018, then weeknum will return 52. Days ( duration as nullable duration) as nullable number About Returns the days portion of duration. Count Days Between Two Dates First,. From the merge, extract the [Change Date]. Commonly used datepart units include month or second. Get Help with Power BI; Desktop; Service; Report Server; Power Query; Mobile Apps; Developer; DAX Commands and Tips; Custom Visuals Development Discussion; Health and Life Sciences; Power BI Spanish forums; Translated Spanish Desktop; Power Platform Integration - Better Together! Power Platform Integrations;. However, the age that you see in the. Take a guided tour. It r esults in age 2, but should be 3 on their. Intenté algo como seguir: Días diff = Table. When you use week as the date part, DATEDIFF returns the number of Sundays between the two dates, including the second date but not the first. . = Duration. Go into the Advanced Editor. @RonRosenfeld You are right. Thank you so much for helping. Returns a date part from a DateTime value. A date in datetime format that represents the start date. Logic 1: IF the column Proposed Complete Date does not contain Good and ART and 2021 and 2020 then DATEDIFF([Today Date],[Request Date], DAY). (when date 1 is the earliest date and date 4 is the latest date). While i use datediff but it shows half negative and half postive integer values. let WorkdayDiff = (StartDate as date, EndDate as date) as number => let DateList = List. Now add a custom column in query editor and write the expression as below =networkdays([Start],[End]) – Step 4: Change Data Type. This can be in either days, hours, minutes, months, quarters, seconds, weeks. Days, Duration. @JKoivu , diff from previous date . Date2. There’s some great documentation here that I strongly recommend you read, but in a nutshell query folding refers to how the Power Query engine inside Power BI can push calculation and transformation logic back to whatever data source you’re using in the form of a query – for example a SQL query if your data source is a relational database. The key here is that you need to do each calculation recursively based on the previous calculation. Interval: How you want the difference to be expressed. Value), DateTimeValue (DataCardValue34. You can follow the below steps to get the number of days between date_entered and exported_date: 1. Community Connections & How-To Videos. It's a bit more complex but replicates the DATEDIFF function for the M language: How to perform a DATEDIFF for POWER QUERY? The code to build your custom Power Query function is in this article The code for each calculation Collec the code for each calculation returns by the DATEDIFF function Write the code into a custom query Create custom queries to copy your code Call your custom function Learn how to use date functions in Power Query M language to create and manipulate the date component of date, datetime, and datetimezone values. Power BI. Can't create datediff measure. In “Table Tools” tab, click on “ New Column ” to create a new Column using DAX. Create a new column. Team, I want to find duration in hours (upto 2 decimal places) from two datetime formatted cells. Resolved Duration = DATEDIFF('FreshService Data RAW' [Created Date],'FreshService Data RAW' [Resolved Date],DAY) Solved! Go to Solution. Well you can simply use the “DATEDIFF” function to calculate the Time Scheduled. How to calculate the date difference between 2 date columns in Direct query? I tried DateDiff function, but this is not supporting in Direct Query. If you're using a label and not a date picker then you have to first convert the label text to a datetime before you can get the difference. Please try this formula: If (DataCardValue42. You need to get the datediff (in days) between the 2 dates, then subtract the number the datediff (in weeks) * 2, and then adjust for edge cases. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. TermDt. Hi PBI Experts, We have two different tables Tabele1 and Table2 in that table i need find Caluclated Column dax function for Date diffence between two tables (Joining date and Action date along with filter is Action Type = "Termination"), Here need to find Date difference (Joining date and Action date) and also apply the filter Action Type. Here is how it works: Day of Year = DATEDIFF (STARTOFYEAR ('Date' [Date]),'Date' [Date],DAY)+1. For that we need to use the Weekday function. 04-21-2020 10:48 AM. To exclude weekends, you need to filter Saturdays and Sundays from both your comparing dates ( xx and yy ). This operation looks at the current date and time on your. From. The problem i am running into is that i only want to display the latest date if the state is "Closed Complete", and the. so the new status B from the old status TG is one day old. Here is the syntax of using this function; DATESINPERIOD (<dates>,<start_date>,<number_of_intervals>,<interval>) Here is a description of input parameters; <dates>: The date field (like many other time. 2) First argument is the DATE field of the current row. I do the same in T-SQL using the following query DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END + CASE WHEN. Please help me to find days between column date and today, i tried below code in custom column function but DateDiff is not recognised code. Days(Duration. HrDiff = DATEDIFF (Table1 [StartTime],Table1 [EndTime],HOUR) HrDiff = DATEDIFF (Table1 [StartTime],Table1 [EndTime],MINUTE) Thanks. If you want to achieve DATEDIFF in Power Query Editor then use Duration. Days formula in the Power Query editor , or avoid custom formulas altogether by using the Power Query Column from Example feature. Filter query between date. Selected. If your date column in the data source is in number data type, filtering will not break query folding. You need to take the values from the datacards for the date and time and put them together to get a DateTime. If start/finish are both before midnight DateDiff Function works fine. 1-7 days = 1 week tenure 8-14=2 week tenure, etc. LocalNow (). Each week from Monday to Sunday has the same week number. 3: Do a straightforward DateDiff for Months. Now in the power query editor, you can see that the column data presented in. It can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. So interested in doing it in DAX measures without creating any additonal columns in power pivot data model. To calculate the datediff between the latest date and today in your table visual, you'll first need to determine what the latest date is. Days will return a number, but the argument for Duration. 2018-04-04 0. . Calculate the Time between Two Dates in Microsoft Power BI (Days, Months, Years) | DATEDIFF DAX #DAX #DATEDIFF #POWERBITo get this, DateDiff should be column, If I create measure I can't show Average of Datediff. Thanks to the PowerAutomate Gods. Insert a index column into the table to help you create DAX. SelectRows (fechas, cada (fechas [date]> = [Event_start_date] y fechas [date]<= [Event_end_date. Power query add column datediff Here we will see how to calculate the difference between two dates to calculate the days count using the Power Query editor in Power BI. Here's an example in Power Query M language: let startDate = #date (2019,1,1), endDate = #date (2023,12,31), durationInDays = Duration. Example:. ・「曜日」の表記方法の設定がわかります. It updates fine each day (the "days until" steadily decreasing) in Power BI Desktop, but the number is frozen on the day I uploaded the report and dataset online. I still need the 'time' aspect of the date to be included in calculation for the following reasons: time difference is Job Booking Datetime - Acceptance Datetime. Power Bi Syntax for DateDiff Function: DATEDIFF(<Date1>, <Date2>, <Interval>) In this example, we will see intervals between two dates based on the Power Bi Dax today function and display the value in the new column of the existing table. Ok, enough preaching. Then click on the New column in the Table tools tab. We will discuss the emergence of generative AI and how we are changing the way makers build and use Microsoft Power Apps with the support of Copilot. com) In the attached file I've used her code in a function and then called that function to work out the NetworkDays for your list of holiday requests. 0. The Power of Community Impact Reflecting on their experiences, Geetha and Ben emphasize the profound impact that community engagement has had on both their professional and personal lives. E. Add one calculated column. I tried. Month([EndDate]) - DateMonth([StartDate]) Regards, Patตัวอย่างในบทความนี้สามารถใช้ได้กับตัวอย่าง Adventure Works DW 2020 Power BI Desktop แบบจําลอง หากต้องการรับแบบจําลอง โปรดดู แบบจําลอง ตัวอย่าง DAX. 4: Add Months to the start date so you can get the remaining days. ‘Date' [Date] means the column named “Date” under the table. Changing the data type of a column in Power Query Editor will break query folding, so please delete the "Changed type" step. From([EndDate]-[StartDate]))) Did I answer your question? Mark my post as a solution! Proud to be a PBI Community Champion. ) (sorry, I don't recall exact syntax of the functions. See the example below. In PowerQuery you can add a custom column and use this formula. No, you can't use DAX. Become an expert!: Enterprise DNA. From([startDate])) ) If you already have Date values, not DateTime values, you can remove the Date. Days(). Measure does not belong to any table. I wanted to replicate the same steps in power pivot. From([EndDate]-[StartDate]))) Did I answer your question?When I'm using the DateDiff Function to get the difference between two dates it works fine, however it rounds up my dates and I would like to round this down. Or perhaps they were thinking of the DAX DATEDIFF function. Azure + Power Apps. Try this: DURATION = DATEDIFF ( SELECTEDVALUE (sai_duedate. Week 1 of the year is the week with the first Thursday of the year. select dim_calendar. Working Days between Dates in Power Query (DATEDIFF) Updated on November 17, 2023 0 Power Query Written By Rick de Groot Rick is the founder of BI. YEARFRAC can be used to compute the current age of a customer based on the difference between the current day and the customer’s birthdate, but because of a bug, it is suggested to use. The code makes use of the Duration. . Power BI Calculated Column based on max date and a filter. ・「日付型」列からカスタム列で「曜日」列を作成する方法がわかります. . You can use the DATEDIFF function to calculate the difference between two datetime objects. Interval. I did the substraction and devide 30 to find monthly tenure in company based on start and end dates. How to get values in PowerBI between two dates in two different columns. Kindly note that the start_date and end. The workbook contains an example of applying the function. How can I ahieve this? I am trying to create categories based on the amount of time difference between the two dayss ie. I am using the formula: DaxTest11 = DATEDIFF('table'[Date/Time Opened],'table'[First Response],MINUTE) returns odd results in a new column I added to the dataset. 01-26-2020 11:38 PM. You will see column tools as one of the tabs. Make sure you don't miss a thing in the community, whether it's the latest News & Announcements, answers to your questions, or topics you are interested in. can som. if possible to show a negative amount when the Date Req is. Power Automate. Weekday = FORMAT (WEEKDAY ('Table (2)' [Create at. Dates (StartDate, Number. Reply. The syntax of the DATEDIFF function is: DATEDIFF (start_date, end_date, interval) Where start_date and end_date are values of the DateTime data type, and interval is a string that specifies the. 2013" (Format "dd. SELECT order_id, order_date, GETDATE() AS todays_date, DATEDIFF(day, order_date, GETDATE()) AS order_to_today -- interval. TimeDiff = SUMX (Table, Table [TimeColumn1] - Table [TimeColumn2] or. 1. All forum topics; Previous Topic; Next Topic;UI for NETWORKDAYS function for Power Query. Please try pasting this code into advanced editor of a new blank query and see if you are happy with the results: let Source =. I have the [Create Date] and [Resolved date] columns, with the two days. Done. 3. 3) Second argument is the maximum date value of all the dates that are less than the current row's date and that belongs to the same device as the current row. Improve this answer. Otherwise, ISO is more than just Monday as first day of the week; the rules are: 1. Cycle Date = 9/1/2022. Start Date/Time End Date/Time. Seconds(duration as nullable duration) as nullable number About. Days(Duration. Your solution: agetest = FLOOR(DATEDIFF(date(2017,02,05), date(2020,02,05), DAY) / 365. No problem so far. The weekend parameter specifies that the weekend is Saturday and Sunday, so those are not work days. We want to see, how many hours was the item on stock. Days ( duration as nullable duration) as nullable number About Returns the days portion of duration. I am using following approach: Time diff = Duration. However, the simplest approach involves leveraging the Custom. Once you compare date/times of the same type, it's as easy as in Excel:Excel 2016 or later for Windows and Microsoft 365. I'm trying to calculate the difference between 2 dates in Power Bi. _____ Nihil simul inventum est et. AllItems,Category. Here will see how to get the current date or today’s date using the Power Query editor in Power BI. From(EndDateTime), //. Days() in Power. -1 euqal to -1. (see below): and my custom column has the the if and else statements as follows. hi, @Anonymous. For example here we have time of buying and time of selling. From(Date. Similarly to the number of minutes. 1. Here is how I proceed: 1. Days(Duration. In this video, we explained How to calculate difference between two dates in Power Query of Power BI. There is no Power Query function like DATEDIFF in DAX, so there are at least two ways to approach this. DAX: Data Analysis. I am trying to calculate the time it takes in minutes for an action to be completed. In the filter query, I want to compare 2 dates like I have date column in SP list and Current date. You can use the Duration functions. When I try to create a column using the following . Webinars and Video Gallery. = Duration. technically i should date and time right It is easiest to use DATEDIFF with MONTH on the DAX side, but in Power Query you can use the formula below. Usage. Substitute the big number with 36000000000 and you will get hour difference, 600000000 minute difference, 10000000 difference in seconds. Ex: New Day to close = CALCULATE( IF ( CLoseDate <> blank(), [your DateDif Formular] , "")) Regards, ManNVSM. I add a column into the data query to generate it row by row but when I try to convert that value to time it errors saying it cant be done on a direct query dashboard?. Duration. 05-28-2018 09:40 AM. Power BI date difference between rows in a single column with conditions. With Power Query, you can get the difference between two dates in Excel. Calculating difference between two dates in weeks in an if statement. ) Important: Retirement of Facebook data connector notice Import and refresh data from Facebook in Excel will stop working in April, 2020. Date (DateTime. 2: Add Years to the start date, so you can then get the remaining months. 1-7 days = 1 week tenure 8-14=2 week tenure, etc. Weekday1. I am trying to insert new column with Date Difference between "Date of First Notification" & "Date". AddMonths, Date. Steps. Days(Duration. 3. Write the below fromula to calculate DATE diffrencess between two dates in Quarter using DAX DATDIFF in Power BI. In response to Anonymous. I know the step of doing it in power query (indexing and doing vlookup) but dont want to follow that method as my data size is 32 lakhs of data. The DateDiff function will give you a whole number of <units> that make up the difference between the two dates; since you already have the days, you only need to use the remainder of the number of hours divided by 24 (using the Mod function ). You can try to define the null value first in the if statement when creating the custom column, like this: = if [Date_Picked] = null or [Date_Req] = null then null else nWorkHours (WHStarts, WHEnds, [Date_Picked], if [Date_Req] <> null then [Date_Req] else null, Holidays)/WHDuration. Accountnumber =. how can I do this. Days (DateTime. In the function below, if the start and end dates are between Monday and Friday (weekdays from 2 to 6), their difference will be in the -4 (start on. I found a post to do the hours and minutes which works, but I tried to keep the same format and add seconds but it's not working as expected. SelectedDate, EndDate. I have the following Table: I want to calculate the service yrs (today-adj_date or today - swd_date) based on the followinf condition: if an employee has an ADJ_DATE, then service yrs should be calculated based on that ( today - adj_date) else if the employee does not have an adj_date then service years should be calculated as today - swd_date. Using DATEDIFF= (column A, column B, DAY) will return the age in days of the columns with an end date, but since there are null values for those still ongoing, it returns nothing for them. This example uses the DateDiff function to display the number of days between a given date and today. Time Taken = DATEDIFF (Sheet1 [Start Time],Sheet1 [End Time],MINUTE)After my test, you could do these follow my steps as below: Step1: Add a group rank column. It appears to take fractional parts of the days and if there is more than half a day remainder, round up the result, which looks like there is an extra day. To do so you use the DATEPART function with the WEEKDAY parameter. If my reply provided you with a solution, please consider marking it as a solution ️ or giving it a kudoe 👍. @Anonymous , You can subtract and add Days date without any function. Hack Together: Power Platform AI Global Hack. returns 3. LocalNow function. Thanks to the PowerAutomate Gods. What I did was create a helper table with Power Query in the following way: For every date (Datum), there is an hour value. Not sure if this works but looking for any way to get this to function. When building the formula, it only wants to suggest. How to perform a DATEDIFF for POWER QUERY? The code to build your custom Power Query function is in this article The code for each calculation Collec the code for each calculation returns by the. 1-7 days = 1 week tenure 8-14=2 week tenure, etc. The second date 2003/11/01 in the second query is also a first-of-month and is included in the result. 2.