Power BI Report Builder Expressions - Date Functions (2022-06-13)

SQLServerCentral Newsletter for June 13, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Power BI Report Builder Expressions - Date Functions]( - [[Re-post] The 10 Benefits of Monitoring for Leaders]( - [Recover Recently Closed Tab in SSMS]( - [From the SQL Server Central Blogs - dbatools - Rich Datatypes]( - [From the SQL Server Central Blogs - Why You (usually) Want a Clustered Index]( Question of the Day - [Getting the OS Version]( The Voice of the DBA  Daily Coping Tip Make of list of what matters most to you this week I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Flawed Data Integration I recently had to travel by airplane after a surgery. My mobility was limited and I requested wheelchair assistance, which I used from Denver to Houston to Amsterdam. Across three airports and two countries, the process was interesting, especially the data integration needed to get things to happen in the real world. As often is the case, there are all sorts of ways that systems don't work well together. In this case, there are actually multiple digital systems integrated. Since airlines contract with passengers, they record their own data that a particular person needs something. However, the actual service is provided by the airport, and each airport has their own system. A classic case of data transfer being needed between multiple systems, in this case three different airport systems. Like the digital applications and databases many of us work with, there are disparate ways the data is handled in different systems. In Denver, the young man pushing me had to update his location constantly, so that there was tracking of how I moved through the airport from check-in to security to the train to the gate. I assume some notification gets to a person at the gate, but it was late. Without my wife, I wouldn't necessarily have had a good way to flag down someone. I assume the delays I had at all gates were more human than digital, but I really don't know. As a "customer" here, I have no idea when data moves from one application to another, much less when the humans in the real world get notified. Many of our digital transformations are taking place with the idea of bringing more transparency to various processes, either digital or analog. Our DevOps changes are to make it clearer how we build and deploy software. Many new app features are built to help employees better understand some part of their business. Others are there to inform customers and give them more reasons to stay our customers and not move on to a competitor. I think it's important that our software developers and UX designers keep evolving and growing to better meet the needs of our customers. I think it's amazing how well software has grown and changed from a one-size-fits-all to adapting and customizing for different users. We all might use and react to software a little differently, so allowing the software to meld with the way we work is incredibly helpful to reducing the friction and flaws in the human to computer integration. This was a good reminder to me of how important data integrations can be between systems, and how much of an impact those friction points can have on a customer. The trip back was much smoother, with people waiting at the airplane doors and showing up in time to get me onto the next plane. That was a wonderful reminder of just how data integration between applications can mesh with humans to create a incredible experience for the customer. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Power BI Report Builder Expressions - Date Functions]( Daniel Calbimonte from SQLServerCentral Learn about expressions and date functions in the Power BI Report Builder. [External Article]( [[Re-post] The 10 Benefits of Monitoring for Leaders]( Additional Articles from Redgate A third-party database monitoring tool is an investment that drives enormous value for the bottom line of a business in ten key ways, from simplifying cloud migration to retaining talent. Here's how. [External Article]( [Recover Recently Closed Tab in SSMS]( Additional Articles from MSSQLTips.com Learn different ways to recover a T-SQL script when using SQL Server Management Studio if SSMS is accidently shutdown without saving the script. [Blog Post]( From the SQL Server Central Blogs - [dbatools - Rich Datatypes]( Cláudio Silva from Cláudio Silva Some datatypes can be shown in so many different forms (think measurament units, dates with short/long forms, or regional settings) that can be difficult to have a standard. What... [Blog Post]( From the SQL Server Central Blogs - [Why You (usually) Want a Clustered Index]( alevyinroc from FLX SQL Note: I originally wrote this a few years ago but never posted it. It resurfaced when I migrated the blog so it’s being posted now. After watching Kevin Kline’s (blog...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting the OS Version I want to query the OS version from T-SQL and use that to decide how other code runs. What is the easiest way to get this version programmatically into a variable? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The Browser Port I have installed a named instance of SQL Server 2019 on a server. I have enabled remote connections, but want to have clients connect automatically to the instance by name as it uses a dynamic port. What firewall change do I need to make for the SQL Browser to function? Answer: open UDP port 1434 Explanation: The SQL Browser service uses the UDP protocol on port 1434 to let the client know which TCP port to connect to. Ref: Configure the Windows Firewall to Allow SQL Server Access - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [SSAS 2017 installation (dedicated)]( - Hi, I selected  just SSAS and backward compatibility/Shared features while doing the installation. I opened the SQL server Configuration manager but the SQL Server Network Configuration is empty. Why there is no protocals showing? SQL Server 2016 - Development and T-SQL [SQL Server 2016 SP3?]( - It’s been almost 4 years since Microsoft released 2016 SP2 and I’m wondering what happened to SP3 given that they usually release one service pack a year during mainstream support. Did 2016 join the Modern Servicing Model like SQL Server 2017? Is 2016 SP 2 the last service pack and from now on we will […] SQL Server 2019 - Administration [Distributed AG]( - Hi, I setup distributed AG between two clusters and did failover testing. During failover I found an issue. Please help me to resolve the issue or provide me links showing if it is limitation. Problem: Configuration: WSFC 1: Node1 and Node2 WSFC2: Node3 and Node4 Availability Groups: AG1(WSFC1). AG2(WSFC2) AG1 Primary node: Node1 AG2 Primary […] [SPLA License question]( - I've never used SPLA lic before. I'm at a new company that requires it to host applications. I've reviewed the User Rights docs. Can someone confirm if I have this correct? 3 node AG Cluster, 2 HA and 1 DR server.  (4 cores each, SQL 2019 Ent) I think I have to license 8 cores, […] SQL Server 2019 - Development [SUM with a ISNULL - Need to show as 0 (Zero) versus NULL]( - Is it possible to have '0' (zeros) versus a NULL in my output?  DECLARE @GroupBy1 VARCHAR(100) = 'Visit Financial Class' DECLARE @GroupBy2 VARCHAR(100) = NULL -- 'Insurance' SELECT [Type] = 'Payments' , [Amount] = SUM(td.Amount) , [Action] = t.Action , [Date] = CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(DateOfServiceFrom)) + '/01/' + CONVERT(VARCHAR, YEAR(DateOfServiceFrom))) , [AR_Bucket] = CASE […] [ODBC Driver 18.0]( -    [IIF statement query using SQL server]( - hey, Using the following statement I can calculate conversions taking place within the same month: IIF(datepart(wk,appl_createddate) <= datepart(wk,appl_conversiondate), 1, 0) however, if I want to return values where appl_conversiondate =< 4 weeks after appl_createddate how would I go about achieving this? Ultimately I'm trying to capture conversions that take place between months as well so […] [How to show table in one row]( - Hello everyone, I want to display the information in the table in one row for each status (show the max create date of status) like the example. I would like to know how to do that Tks, Gilad [Select from a table that might not exist]( - Hello! I'm writing a query. As per the logic in my SELECT statement there should be a column from a table that might exist. If it does not I should select Null instead Smth. like the following non compiling code DECLARE @NoTable bit SET @NoTable = 1 SELECT e.SN as ESN, CASE WHEN @NoTable = […] SQL Azure - Development [Land and process 100's of files using Synapse]( - Hi fellow cloud people We are looking at moving from the land to the cloud we have 100's of files and SQL tables to load into the Data Lake Gen2 then process into the staging and presentation zones (I know names) and then on to the SQL Data warehouse We are looking for the best […] Amazon AWS and other cloud vendors [Lambda Error Handling with SQS Trigger]( - Hey everyone, so I am trying to build a queue (fifo) with a worker lambda. Simple enough and it works. But in case of an error, I want to reliably mark the failed workload as failed in a database (no retries!). For some errors, I could just use a try-catch block. But in case of […] General Cloud Computing Questions [Question about which cloud provider to get certified with.]( - Hi all! An opportunity within my company recently came up that would allow me to pursue a foundational certification for either GCP or AWS (AWS Certified Developer Associate vs GCP Associate Cloud Engineer). I wanted to reach out to ask if anyone had valuable insight about which certification would be most worthwhile to pursue. For […] Integration Services [SSIS Expression to get current date with out timestamp]( - I have folder path c:\Employee\Students20220507023108 i am using this folder c:\Employee\ in for each loop container and i am planning to bring the foldername dynamically like created 2 variables folder path- c:\Employee\ folderName - Students Another new variable that holds entire path along with date. but the problem is my folder keeps on referring to […] [Concatenate value]( - Hi all, I have derived column in the package (eight-bite integer value): (Test_column % 1000000000). Example, we have a code 111100000001234566. Using this expression we received 1234566. But now I need also fetch the first 4 digit and added this expression (Test_column % 1000000000). So, I want to receive this value 11111234566. Could you please […] PostgreSQL [Turn on Query Execution Time Command]( - Hey, I am in the learning state for PostgreSQL and looking to turn on query execution command examples. I have added one example taken from this here. Can anyone tell me, Is it the right command example? user_ab=# \timing Timing is on. user_ab=# select count(*) from film; count ------- 1000 (1 row) Time: 1.495 ms […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -