Category: Dates and Numbers

PARSE, CAST, and CONVERT

Published 2019-08-28 by Kevin Feasel

Max Vernon gives us three ways to change data types:

PARSE provides a mechanism to convert a wide variety of character based dates into a datetime data type. From the Docs:
Returns the result of an expression, translated to the requested data type in SQL Server.
Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

PARSE has its value, but comes at a cost, as Max shows.

Comments closed

Two Takes on Power BI Time Dimensions

Published 2019-08-28 by Kevin Feasel

Brett Powell gives us a version of a time dimension in Power Query:

Like most dimensions in a data model, users will generally begin their analysis of time at a high level (e.g. hour) and then filter and drill their way into greater levels of detail, often leveraging hierarchies built into the model and/or drilling functionality in tools like Power BI. To support analysis by multiple grains and hierarchies, the query produces columns which group the seconds into hourly quartiles, hours, and minutes.

Gilbert Quevauvilliers has a script to generate a time dimension as well:

Below is the syntax to create a Time Dimension Table in Power Query
I had a requirement where I needed to create a Time Dimension for a customer. Most of the time I only need the date. Upon searching I could not find a resource where they had created the time dimension only using Power Query.

Gilbert’s gives you a key insight into the value of time dimensions: breaking everything out into periods. 5-minute intervals, 15-minute intervals, or however the company looks at data.

Comments closed

Building SQL Agent Dates and Times

Published 2019-08-22 by Kevin Feasel

Kenneth Fisher goes over one of the things in SQL Agent which make me shudder:

Occasionally I’ve seen date and time stored separately as integers. This had some practical applications back before we had date and time data types but there’s still lots of legacy code out there that use them (I’ll give you a really really common example in just a minute).
Unfortunately, you can’t convert datetime to date and time ints directly but it isn’t all that difficult.

Kenneth notes the function you can use as well as a quick query to calculate duration.

Comments closed

Finding Gaps in Dates

Published 2019-07-15 by Kevin Feasel

Jason Brimhall shows how you can find gaps in your data:

This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set which happens to be my missing question days. Only, I have added an extra output in the second query to let me know the day of the week that the missing question occurred on. Maybe I forgot to enter it because it was a weekend day or maybe I opted to not create one at all because the day lands on a Holiday. Let’s take a small peek at the results.

This is a good use for tally tables (or for a calendar table, which is basically a date dimension called something else so you can feel comfortable dropping in a non-warehouse system).

Comments closed

Getting Prior Year’s Year-To-Date with DAX

Published 2019-05-13 by Kevin Feasel

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year:

Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.

Read on for a detailed analysis, including where you might go wrong.

Comments closed

SARGability and Date Functions

Published 2019-04-23 by Kevin Feasel

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table:

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.
The examples I’m going to look at are for YEAR() and MONTH().

Read the whole thing. Maybe “go to brunch” in the middle of it for maximum effect.

Comments closed

Extended Event Duration Units of Measure

Published 2019-04-11 by Kevin Feasel

Emanuele Meazzo shows how we can find out whether that duration column is milliseconds or microseconds:

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.

Comments closed

Dates in Base R

Published 2019-04-10 by Kevin Feasel

Michael Toth explains some of the functionality available in base R (that is, not packages like lubridate) for working with dates:

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

Comments closed

The Pain of DST and the Lessened Pain with AT TIME ZONE

Published 2019-03-06 by Kevin Feasel

Bert Wagner shows how you can use AT TIME ZONE as of SQL Server 2016 to make dealing with Daylight Savings Time a little less painful:

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.

Comments closed

Using SWITCHOFFSET

Published 2019-01-31 by Kevin Feasel

Doug Kline has a video and T-SQL script around date/time offsets and particularly the SWITCHOFFSET function:

— 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.

Comments closed