DATEADD Truncates the Number Parameter: #SQLNewBlogger

Posted on March 19, 2025 by way0utwest

This was an interesting thing I saw in a Question of the Day submission. I hadn’t thought about the issue, but apparently DATEADD truncates values rather than rounding them. I’m not sure why that is the case, but it is.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

Imagine that I have someone enter a value for the number of hours to include in a report. I enter 5 and the report divides this in half to go back 2.5 hours and forward 2.5 hours. I run this code at the top of my code block:

DECLARE @hours NUMERIC(4, 2) = 5;
DECLARE @start DATETIME, @end datetime
SET @start = DATEADD (hour, -@hours / 2, GETDATE ())
SET @end = DATEADD (hour, @hours / 2, GETDATE ())

Now, what do you think are the resulting start and end times? I’d assume this works and the function sorts out how much of an hour is .5 or .4 or whatever.

Here’s the interesting result. Look at the time interval in the end result.

2025-03_0091

It’s 4. I entered 5 hours, but I get 4 hours. I bet a lot of us would let this bug slip through as reading the datetimes we’d miss this wasn’t actually 5 hours.

Apparently DATEADD actually truncates a non-integer value. The parameter notes that the 2nd parameter, the number to add to the date value, resolves to an integer. It also notes that DATEAD truncates, not rounds, values that have a decimal fraction.

Those are two very important distinctions. That could result in calculations that are way off from what people expect if you are trying to include data in a query and you are trying to do parts of time. You might need to separately calculate all your different date/time parts.

If you need to do fractional work with dates, you can’t use DATEADD.

To me that seems lacy, but is it? Let me know.

SQL New Blogger

This is a short example of something that a person pointed out to me, and I never knew. I decided to make a quick test (the code above) and then write about this. I could have included other examples, or shown how this might mess up different situations in my code.

You could do the same thing in 30 minutes or less and point out an interesting piece of knowledge that your future employers might find interesting. They might even want to interview someone that learns things like this.

Posted in Blog | Tagged , , | 1 Comment

Lower Your Attack Surface Area

Posted on March 19, 2025 by way0utwest

It’s no surprise that our systems are under attack by all sorts of criminals. Some organized, some opportunistic, some just aiming for vandalism. We need to protect our digital systems to prevent issues, and a part of better protection is reducing the number of places that are vulnerable. Those places include databases.

This article discusses the rising costs of data breaches and the increased frequency of attacks. It also examines the increasing number of regulations that are demanding proof of stricter security measures. It can be hard enough to defend production systems, let alone protecting dev/test environments. I see an increasing number of organizations that limit access to production systems, even to the point that this impedes some of the daily work habits of technology professionals, but that is probably a good thing. Too many of us are too lax when it comes to security.

There are lots of approaches to getting better at security, but one of the easier ones is to avoid making copies of sensitive data. About half of you (I hope it’s not more) still use production restores in dev/test environments. That’s a tripling of the places your data could be attacked if you restore a database to a development server and a test server. If could be even worse if you make more copies.

An easy solution in today’s world is to build a better test data management process for either anonymizing and obfuscating your sensitive data or generating synthetic data. Both have their challenges and I suspect that most organizations need a combination of both approaches to both protect their data as well as build better software for their customers. After all, a huge amount of bugs are data related, where developers have not tested their software against enough different data elements. Both synth data and anonymized data help here to produce enough different edge cases that your testing is thorough enough to increase quality.

Of course, you need extensive testing, which means automation. Ideally an automated DevOps flow that subjects your software to increasingly complex tests as it moves through your pipeline to ensure it’s ready for release. This also means a good set of test data, not only for QA, but for automated tests. You need a test data management strategy.

Securing digital systems is a complex task, but we ought to try and make it easier on ourselves by developing good habits in how we manage both access and by limiting the copies of sensitive data.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Lower Your Attack Surface Area

Can an AI Help Me Find a Job?

Posted on March 17, 2025 by way0utwest

I’m not looking for a job, but I ran across an article about using AI tools for a job search. So I decided to try it out. I followed a couple of those prompts and get some results. Here is a set of things I did.

Salary Calculators

I ran this prompt from the article: What are some of the best salary calculators for a Data/SQL Server professional? Provide a list of your top 4 recommendations and the pros and cons of each one. Provide any helpful links. Format as a table.

The results? Mixed. Here is what I got from my local Deepseek model. Note, I didn’t get a table.

2025-03_0123

From Claude:

2025-03_0124

From Perplexity

2025-03_0125

Are these helpful? Well, I knew about GlassDoor and the BLS. Didn’t know Robert Half has a calculator. If I had done this a few years ago, I would have Googled this. When I did, I saw Payscale, Zippia, and CompTia. Ziprecruiter had hourly salaries, US News had a link. None of the ones in the table were there.

Both the Payscale link (Google) and Robert Half gave me similar results. Is the AI more helpful? It gave me a few things to think about, but it didn’t find some of the top Google links. Not sure what to think about that.

My Worth

I decided to ask this prompt: I am a senior database administrator with over 20 years of experience working with SQL Server in large and small companies, handling a variety of operational responsibilities. I have developed and implemented high availability solutions, automated server builds, understand DevOps style database development and GitOps management of servers. I have managed replication, built ETL pipelines, and implemented role-based access controls. What is the market value of my skill set?

The local model didn’t really help here. It gives me some generic things to think about and consider, and some ideas of where to look, but no real links.

Claude:

2025-03_0127

Perplexity:

2025-03_0128

Adding Skills

From claude:

2025-03_0129

And perplexity:

2025-03_0131

Those are interesting ideas. I’m sure I could follow this up and get more details on some of these and perhaps kickstart my learning. These are areas an AI might be helpful to help guide you or develop a plan. Certainly a friend might do this, but it’s tedious work. Getting some links, assembling a plan, etc.

Claude builds a detailed plan on the right side and describes it on the left The plan has a lot of links to documentation for tools below the phases:

2025-03_0133

Preplexity gives me a plan, with links as footnotes.

2025-03_0132

Negotiation

I used this prompt as is from the article: I am making $98,000 with 3 weeks of PTO now. I want to ask for an 8% raise and an additional week of PTO. I also want to work from home one more day a week, especially if I can’t get any extra PTO. What guidance do you have for making this request? What other rate negotiation best practices should I keep in mind?

Claude: a decent plan to get prepare.

2025-03_0135

Perplexity: very similar results

2025-03_0136

This isn’t a lot different from advice I’d give, but I wouldn’t write this all down. If someone told me this, I might forget some things. A plan is always good, and the AI has given me a decent plan. I might copy this and run it by friends, who are more likely to mark it up or add to it rather than build the whole thing.

It’s an assistant to getting started and a pretty good one.

A Cover Letter

These are a pain, and I always struggle to get one moving. Here are the results from this prompt:  Given my experience, help me write a cover letter to apply for a DBA job

Claude: Again, an explanation on the left and a result I can start with on the right. It’s  a bit wordy and AI-like, but I can adjust that.

2025-03_0137

Perplexity: Simpler, but effective.

2025-03_0138

Note, I didn’t paste anything else in, the AI remembered my experience from earlier in the chat. That’s way, way, way better than working through a search engine.

Summary

For most of us, (I hope) searching for a job is a rare thing. It’s always a pain, and it can be stressful. It’s also easy not to be organized and forget things. A GenAI can assist you with more patience than your friends or family might have. It can give you a good start, but please don’t just copy/paste this stuff or follow it blindly. Ask fellow humans what they think of these recommendations.

The GenAI models are trained on lots of data, and they can function as a sounding board, but they aren’t bringing creativity, and your answers will look a lot like other people’s answers. Lots of people will use an LLM to help them here, so make sure you tweak things to stand out a bit.

And build your brand, and let GenAI help you. Don’t let it write blog posts, but it can sketch things out that you edit and clean up. That editing and cleanup skill will help you in interviews and jobs, so don’t forget to polish the final product yourself. Let the GenAI be an assistant that gets you started and that’s it.

Posted in Blog | Tagged , , , | Comments Off on Can an AI Help Me Find a Job?

Widespread New Technology Adoption

Posted on March 15, 2025 by way0utwest

This week I saw an article at Ars Technica that noted in 2025 that electrical vehicle sales had increased 28% in the US. That’s quite a level of growth in adoption of this technology, especially given how set in their ways the US can be and how often these transition away from ICE vehicles has been politicized.

I’ve had a Tesla for a little over 3 years and I think it’s the best and most fun car I’ve over owned. The technology and convenience are amazing, and I still enjoy driving the car around. For the most part I don’t have range anxiety, though I recently skipped using it on a weekend trip. The car has proven to be very inexpensive to operate and I wouldn’t hesitate to purchase another EV.

Apparently lots of other people feel the same, though not everyone. About 75% of new vehicles sold are still ICE vehicles, which I completely understand. Not everyone has access to regular charging at home and the cost of these vehicles, while only slightly more than ICE ones, is still much higher to start. That hasn’t stopped many people from evaluating and choosing to purchase a car that runs on battery technology. However, plenty of people just don’t want to adopt new technology.

I see the same thing in the tech industry. GenAI has been quite the disrupter the last few years, but many people don’t trust models and don’t want to try to use them. I’ve also seen many people who blindly trust this new technology, which can be even worse.

I’ve seen the same type of attitude in the past with DevOps, PowerShell, NoSQL and other technologies. Many people are slow to adopt new technology, for a variety of reasons. It might be they aren’t sure the technology really offers any improvement, which is understandable. After all, if something new doesn’t actually provide significant benefits, is it worth an investment? After all the investment is a cost and there are likely some downsides.

There are other people who are resistant to any change or unmotivated to bother, preferring to stick with what they know and are comfortable with using. I don’t quite understand this view, as it’s entirely possible we originally learned a poor way of doing things. It’s also likely that as technology changes, there are improvements made from our learnings in the industry. Certainly doctors, mechanical engineers, architects, lawyers, and more adopt new practices because their professions evolve and move in new ways, so why should technology professionals be different?

I’d like to think that the reason many people don’t bother to learn new (or improve existing)  skills or adopt new tools is that they don’t have time. We’re all under pressure to get work done, and often without any planning for our various administrative work and meetings, let alone time to invest in ourselves. However, I will say that finding a way to carve out time to read, experiment, and practice skills has helped me improve my own career prospects, as well as helped me convince employers to match my investment with their own time, resources, and funding.

Steve Jones

Posted in Editorial | Tagged , | Comments Off on Widespread New Technology Adoption