— 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.
The time zone name is taken from a list maintained in the following Windows registry hive:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
Note: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry by intercepting the API calls and returning the expected value(s).
We can also use a Transact-SQL (T-SQL) query against the system view
sys.time_zone_info, which uses the information from the registry hive. This is the recommended method if you do not have access to the registry hive.
Click through for a couple of examples.
What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the
DATEPARTfunction is returning the wrong value?
This is why you want to stick with four-digit years. But if you’re stuck with two-digit years for some reason, Claudio explains how you can get Excel and SQL Server to return the same results.
It’s a collection of inline table value functions that generate different types of calendars, with a number of properties that could be relevant for a calendar dimension. Each function has a unique date column, so you can join the functions you need together in a view or a procedure. The functions are:
Dates: a plain gregorian calendar.
Fiscal, annual: a gregorian, year-based calendar where you can define the start of a year, like a corporate fiscal calendar.
Fiscal, 4-4-5 or 52/53: a week-based calendar where years comprise four quarters of 4+4+5 weeks respectively.
Indian national calendar
Dates of Catholic and Orthodox easter
I was going to jokingly be shocked that this list didn’t include the Hebrew or Islamic calendars, but then Daniel had to ruin my fun by explaining why not. Check it out and when you’re ready to give it a try, head over to his downloads page.
The solution is part of my calendar/date dimension code, and it is used to do relative positioning over date periods. For example, say you have the need to get data from the 10 days. You can definitely use a simple between to filter the rows, and a bunch of date functions to group by year, month, etc., generally all of the “normal” groupings. But using a calendar table allows you to prebuild a set of date calculations that make the standard values easier to get, and non-standard groupings possible. The technique I will cover makes moving around in the groupings more easily accessible. Like if you want data from the last 3 complete months. The query to do this isn’t rocket science, but it isn’t exactly straightforward either.
For the example, I will use the calendar table that I have on my website here: http://drsql.org/code in the download SimpleDateDimensionCreateAndLoad, and will load it with data up until 2020. Here is that structure:
Read on for examples of usage. This is an example where thinking relationally differs from thinking procedurally—imagining date ranges as pre-calculated sets isn’t intuitive to procedural developers, but it can give a big performance boost.