A Fine Slice Of SQL Server
Kevin Wilkie is waiting for the calendar to change:
In SQL Server, we’re used to finding the end of the month via a few different methods. We can always use the DateAdd and DateDiff functions to get our data – which sometimes takes a bit of work – or we can use the EOMonth function.
Read on to see what tools are available for Snowflake users.
Comments closedItzik Ben-Gan has a new challenge:
The task involves developing a stored procedure called
GetBalancesthat accepts a parameter called@accountidrepresenting an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.
My first thought was last observation carried forward, which is now available in SQL Server 2022 (Itzik’s solution 2). I kind of thought of solution 3, though did not think through the mechanics of how it’d work and so I get no credit there.
Comments closedSteven Sanderson works with dates:
Dates and times are essential components in many programming tasks, and R provides various functions and packages to handle them effectively. In this post, we’ll explore some common operations using both the base R functions and the lubridate package, comparing their simplicity and ease of understanding.
I personally prefer the lubridate style of date operation, but it’s nice to have options.
Robert Cain keeps track of time:
I’m still working on my documentation for my ArcaneBooks project, but wanted to have something for you to read this week, so decided to show you how to create an elapsed timer in PowerShell.
It can be helpful to determine how long a process runs in PowerShell. You can use it to determine what parts of code may need to be optimized, or gather metrics around your functions.
This is the same stopwatch operation which is available in .NET generally, so if you’re using C# or F#, it’s the same basic process.
Comments closedSteven Sanderson talks dates and times. First up is an overview of how built-in date functionality works in R:
In this post, we will cover the basics of handling dates and times in R using the
as.Date,as.POSIXct, andas.POSIXltfunctions. We will use the example code below to explain each line in simple terms. Let’s get started!
The second post covers the Date type, as well as date plus time:
In the first part of our code, we use the
as.Date()function to find the next Mother’s Day. Since we don’t need to consider specific times, we can simply use the Date class, which simplifies the process. We create a vector with two elements:startMothersDayandendMothersDay, both set to “2024-05-14”. This represents the range of Mother’s Day for the year 2024. Finally, we store the result in the variableNextMothersDayand print it to the console. Voilà! We have the next Mother’s Day date.
Note that this isn’t a post about date calculation, such as finding the Nth Sunday in the month of May
Comments closedBill Fellows fights with the language:
It seems so easy, I was building json in SQL Server and the date format for the API specified it needed to have 3 millsecond digits and the zulu timezone signifier. Easy peasy, lemon squeezey, that is ISO8601 with time zone Z format code 127
SELECT CONVERT(char(24), GETDATE(), 127) AS waitAMinute;Running that query yields something like 2023-05-02T10:47:18.850 Almost there but where’s my Z? Hmmm, maybe it’s because I need to put this into UTC?SELECT CONVERT(char(24), GETUTCDATE(), 127) AS SwingAndAMiss;Running that query yields something like 2023-05-02T15:47:18.850 It’s in UTC but still no timezone indicator.
Read on for several attempts and what finally did the trick.
Comments closedMarco Russo and Alberto Ferrari shave of fractions of a cent:
The first reason to choose a data type is the range of numbers supported and the precision. However, the result of a mathematical operation may produce a number that cannot be represented in the chosen data type, which requires a rounding operation. Therefore, the result of one same sequence of operations can produce different results depending on the data type and the order of execution. In this article, we discuss the typical rounding behavior for each data type and how to avoid possible issues in your DAX formulas because of any differences from the results you may have expected.
Read on to learn what granularity limits exist for integers, fixed decimal numbers, and floating point operations.
Comments closedAllison Kennedy shares a variant of DimTime:
Today’s post is going to be short and sweet. I’m simply publishing my version of a DimTime table for Power BI.
Thanks to Radacad for posting a great article on why we need a DimTime table, along with their script for creating one in Power BI.
If you don’t already have a DimDate table, you can find my version in my DimDate: What, Why and How blog article. I update the script occasionally with new requests.
Click through for the script.
Comments closedKevin Wilkie is ready for that end-of-month paycheck:
When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.
Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.
Comments closedI have been teaching a T-SQL 101 class and for the homework, we asked the students to get all the records where our heroes had a birthdate between 1995 through 1999. I expected something like this:
[…]
Imagine my surprise when one of the students turned in this:
SELECT FirstName, LastName, Birthdate FROM Heroes WHERE Birthdate BETWEEN '1995' AND '1999'When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.
Click through to see what it returns and how that’s not quite right.
Comments closed