Shortchanged with International Money in SQL Server

Published on: 2018-09-25

Imagine you have to perform some salary analysis for your employer International Mega Corp.

The data you have to work with looks something like this:

Why are the salaries stored as nvarchar and formatted with commas, spaces, and periods?

Great question!  Someone wanted to make sure these amounts would look good in the UI so storing the formatted values in the database would be the way to go…

Pretty for the UI, not really great for needing to do analysis on.

Thanks to Zanoni Labuschagne, one of the subscribers to my YouTube channel, for recommending this topic!

CONVERT!

I’m a firm believer that money values should always be stored in the decimal datatype.  I can’t think of a time where I wouldn’t care about the precision and accuracy of money.

So let’s try converting our salaries to decimal(10,2):

Well, that didn’t work.  Maybe converting to floats will work as a quick fix?

Nope that didn’t work either

(sidenote: I’m OK with that though – I don’t think float should ever be used for storing money.  If you want to see a quick example of why float math is problematic take a look at this (and for more detail read about it here):

Ok so those didn’t work.  What if we try converting to the money datatype – that should work for being able to read these money formats right?

50% correct…!  Our employees in Turkey are being seriously underpaid with conversion though.  I’m kind of glad to not have to rely on this solution though since the money datatype has its own fair share of problems as well.

Time to get Cultured

SQL Server’s TRY_PARSE function might be able to help us, but first we need to create a relationship between each country’s money formatting and it’s culture code:

And finally our SELECT query:

Success!  Our salary values are now perfectly converted into the decimal datatype without the need for any ugly REPLACE(), SUBSTRING(), or other string parsing functions.

While this carefully curated demo correctly converted all of our values, it’s important to always test that the culture value you choose correctly formats your string formatted number.  For example, Wikipedia leads me to believe that the  Danes write their numbers like “6 338,70” SQL Server’s culture definition doesn’t convert this correctly:

In those instances, you may need to substitute another culture code to get the correct conversion to occur.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Who Stuck These Letters In My DateTimes?

Published on: 2017-03-28

How to handle JSON DateTimes in SQL Server 2016

Parsing, creating, and modifying JSON in SQL Server 2016 is really easy. JSON dates and times are not.

Coming from a predominantly SQL background, the JSON DateTime format took some getting used to, especially when it came to converting SQL datetimes to JSON and vice versa.

The remainder of this post will get you well on your way to working with JSON date times in SQL Server.

Breakdown of JSON date/time

In SQL Server, datetime2’s format is defined as follows:

JSON date time strings are defined like:

Honestly, they look pretty similar. However, there are few key differences:

  • JSON separates the date and time portion of the string with the letter T
  • The Z is optional and indicates that the datetime is in UTC (if the Z is left off, JavaScript defaults to UTC). You can also specify a different timezone by replacing the Z with a + or  along with HH:mm (ie. -05:00 for Eastern Standard Time)
  • The precision of SQL’s datetime2 goes out to 7 decimal places, in JSON and JavaScript it only goes out to 3 places, so truncation may occur.

Now that we know the key differences between SQL datetime2 and JSON date time strings, let’s explore common transformations when working with JSON data in SQL.

Parsing JSON date time into SQL datetime2

The most common operation I perform with these new JSON functions is parsing, so let’s start with those. Let’s see how we can parse the date/times from JSON using SQL Server 2016’s JSON_VALUE() function:

Inserting SQL datetime2 into JSON

Taking date/time data out of JSON and into SQL was pretty easy. What about going the opposite direction and inserting SQL date/time data into JSON?

Modifying JSON date time with SQL

So we’ve seen how easy it is to parse and create JSON date/time strings, but what about modifying JSON data?


Overall, working with JSON dates/times is really easy using SQL Server 2016’s new JSON functions. Microsoft could have done a really bad job not following the ECMA standards, but they did a great job crossing their T‘s and placing their Z‘s.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!