If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea.
But just like there are slightly different rules for CAST and CONVERT with dates, the repercussions of the function also vary.
Read the whole thing. Maybe “go to brunch” in the middle of it for maximum effect.
Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter.
That’s why I’ve added to Tsql.tech Github repository the following code that extracts the descriptions from XE DMVs in order to identify the unit of measure
Click through for the script as well as the results against Azure SQL Database.
When working with R date formats, you’re generally going to be trying to accomplish one of two different but related goals:
1. Converting a character string like “
Jan 30 1989” to a Date type
2. Getting an R Date object to print in a specific format for a graph or other output
You may need to handle both of these goals in the same analysis, but it’s best to think of them as two separate exercises. Knowing which goal you are trying to accomplish is important because you will need to use different functions to accomplish each of these. Let’s tackle them one at a time.
There are some good insights in the post. H/T R-bloggers
The fallacy above is that I said our two datetime2’s are in UTC, but SQL Server doesn’t actually know this. The datetime2 (and datetime) datatype doesn’t allow for time zone offsets so SQL Server really doesn’t know what time zone the data is in.
Using AT TIME ZONE on a datetime2 without offset information causes SQL Server to “…[assume] that [the datetime] is in the target time zone”. That explains why the two datetime2s above, intended to be in UTC, are actually seen as Eastern Daylight Time by SQL Server.
Read the whole thing. Dates and times are a lot more difficult than they first appear. And then they turn out to be a lot more difficult than that.
— so, before SWITCHOFFSET existed, …
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [EST the easy way], TODATETIMEOFFSET(DATEADD(HOUR, -5, SYSDATETIMEOFFSET()), '-05:00') AS [EST the hard way]
— so, thinking of a DATETIMEOFFSET data type as a complex object
— with many different parts: year, month, day, hour, time zone, etc.
— it looks like SWITCHOFFSET changes two things: time zone and hour
This was an interesting video. I typically think entirely in UTC and let the calling application convert to time zones as needed, but if that’s not an option for you, knowing about
SWITCHOFFSET() is valuable.
Quick tip for DST Refresh Date function Power BI Service. I’ll put the code up front, and explain it below. I’ll also say a bit about how to use it at the end. The United States and other places, like Australia, have a pesky thing called Daylight Savings Time. This means that in Central Time US, the offset from Universal Time Coordinated (UTC) is sometimes -6 and other times it’s -5. While Power Query can convert time zones, it doesn’t handle DST. And, my users like to see when the reports were refreshed as a step in evaluating data quality. In 2019, US DST is from March 10 – November 3 (2 AM local time). So, the functions here need to be updated every year.
As promised, here’s the custom function.
Click through for the custom function and a nice explanation of how it works.
Query Store retains query performance data at the plan level. This data is then broken out into intervals of time, determined by the INTERVAL_LENGTH_MINUTES setting. The time intervals are found in the sys.query_store_runtime_stats_interval system view, and the start_time and end_time columns are of the DATETIMEOFFSET data type. This means that the date is time-zone aware, and in Query Store the data is stored as UTC. Now why does all of this matter? Because handling dates in Query Store is important if you’re going to query the data directly.
Click through to see a query of what this looks like, as well as a few tips on parsing the data.
The only functional difference between them is that the
DATEDIFF_BIG()returns values as a
BIGINT, for results that exceed the boundary of an
INT. Keep this in mind when deciding which one to use. For example, the maximum number of seconds an
INTcan hold is 68 years, while a
BIGINTcan comfortably store the number of seconds in 10,000 years. This becomes especially important when dealing with microseconds and nanoseconds.
The rest of the post will use
DATEDIFF()to refer to both functions.
I think this might be the first time I’d read about
DATEDIFF_BIG()and I’m not aware of ever having used it. But hey, it could make sense if you need to track more than 2 billion microseconds.
As with similar functions,
DATEADDcan do arithmetic on dates as well as times. The syntax is straightforward:
DATEADD (datepart, number, date)
numberportion must be an integer, and it must be within the acceptable range of values for the date part.
Click through for a few examples.
Manipulating date and time in T-SQL is a daily and very common task that every DBA, SQL Developer, BI Developer and data scientist will come across. And over the years, I have accumulated many of the simple date or/and time manipulation combinations of different functions, that it is time, to put them together.
Don’t expect to find here anything you haven’t used or seen – especially, if you are a long time T-SQL developer. The point is to have a post, that will have a lot of examples on date and time manipulation on one place. And by no means, this is not the definite list, but should be quite substantial and the code on Github repository will be update.
The list will be updated on my Github, and therefore this blogpost might not include all. In all of the following examples I will be using function GETDATE() to get the current datetime, unless the examples will have stored dates. Therefore, some of the examples or screen-prints will be different from yours.
This mostly focuses on the
DATETIME type rather than
DATE, but there are a few
TIME uses. Check out Tomaz’s repo for more.