SQL Server Operations Studio and VSCode: The wrong default datetimeoffset format

This post is to answer the question: “You are used to seeing in the format of yyyy-MM-dd right?” that I have raised on my blog post Don’t cutoff yourself with dates in T-SQL – Did you know….

As you could see from that blog post, my screen shots were from VSCode and in this case using mssql extension, but this happens also on SQL Server Operations Studio.

“But why are my datetimeoffset values on VSCode being showed in that format?” (dd-MMM-yyyy)

The short answer is because it relies on your regional settings.

This means that whatever settings you have set for your date when you open the VSCode or SQL Operations Studio this will be used to show the output from your datetimeoffset columns.

I’m being specific when I mean datetimeoffset

The DATETIME and DATETIME2 types already display always in yyyy-MM-dd format like SQL Server Management Studio.
Once again, remember, I’m talking about default output not if you use a CAST, CONVERT or FORMAT function to manipulate the results.

How can we fix this?

When I was looking for this behaviour I did some research and found that a similar problem was raised but regarding DATETIME2. You can see it here – issue #570.
With this in mind, I decided to open an new issue (#1139) on the vscode-mssql extension repository on GitHub and point to the other one already solved.

If you identify yourself with it, please add your to the issue.

Now, we need to wait to see the evolution and, hopefully, a fix will be included on a upcoming release.

Summary

Always try to use an unambiguous date format like “yyyy-MM-dd”.

Remember, copying things that can have double meaning (incorrect format) can lead to unexpected results like I have shown on the other blog post.

And last but not least, if you think is a bug/missing feature please take the time to fill an issue. It will help you an others for sure!

Thanks for reading!

Learn, Evolve and Giving Back – TSQL Tuesday #102

https://scribnasium.com/2018/05/giving-back-t-sql-tuesday-102-invite/

This month’s T-SQL Tuesday is brought to us by Riley Major‏ (b | t) and he encourage us to talk about how we are helping by giving back to our community.

This is the 102nd edition of TSQL2sDay – an Adam Machanic’s (b | t) brainchild.

Let’s go back for a moment:

I have heard about SQL Server the first time back in 2003 when I was on the high school and I had a database class. Was a slow start and, at that moment I haven’t imagined that would follow that path professionally.

Learn

At that time I didn’t even know that a community exists, but when I went to professional course (level IV – it’s a degree before bachelors) on 2006 that was based on Microsoft technologies, .NET (Web and Windows forms) and I had a database class using SQL Server 2000!
That was when I started using more and more the internet for study, found some blogs posts and the SQLServerCentral. So I can say that I started consuming the knowledge from the community back on 2006.

A year later I started my intership in what was my first IT job. At the time I needed to work with a bit of everything but more focused on SQL Server development. The guy that was on my place before I arrived took a manager position but he was a big lover of SQL Server and I learnt a LOT from him! At the time he subscribed the SQLServer Magazine in paper! (later the name changed for SQLServer Pro).

SQLServer Magazine October 2007

SQLServer Magazine
October 2007


You can found the maganize archives in PDF format here.

That was when I start knewing who people like Itzik Ben-Gan and Kalen Delaney were!

Evolve

During about five years I was a compulsive consumer of blog posts related with SQL Server development but most of the time was when I had a problem and wanted some guidance on the solution. During that time I haven’t shared too much knowledge on the internet.

Then, 2012, I went to my first SQL Port user group meetings and my first ever SQL Saturday, 115 Portugal. Those were my first real contact with SQL Server community and where I met some speakers and started to join the montly meetings regulary.

Giving back

At 2015 I joined Twitter and decided that I wanted to share my knowledge even more. So after being approached several times I took courage and did my first public presentation for SQL Port user group in Lisbon and right after I submitted a session for SQL Saturday Oporto 2015 and I was accepted.
You know, start small and grow…so I started talking in Portuguese for the user group and then went to SQL Saturday where I did the first public speech in English.

Now, we were in 2016 and was the first edition of TugaIT (the CFS is open for this year – Summer Edition) and at that time I was “just” a volunteer but I had a gut feeling – I have discovered, few months earlier, that dbatools was a thing and decided to go and talk with the creator Chrissy LeMaire – I had written a couple of PowerShell scripts to help me and decided to ask her if she has the intention to extend the module to best practices and we talk for like 1 hour, exchange contacts and started talking furiously about dbatools and then…I was doing a PR with a full command (Expand-DbaTLogResponsibly) and this was the time I felt I was doing my first “more international” contribution to the community.

Since then I have being more active on the community mainly with SQLServer and PowerShell stuff.

Some contribution points:

What you will do?

As you can see I didn’t born inside the comunity, I have grown because of it and decided to start giving back less then 3 years ago.
It hurst? Nothing!
It helps? A lot!
Help me be a better professional? For sure, I have learned so much from many different sources and every day is a new learning day!

Thanks for reading.

TSQL Tuesday #96: Folks Who Have Made a Difference

tsql2sdayThis month’s T-SQL Tuesday is brought to us by Ewald Cress‏ (blog | twitter) and is all about “folks who have made a difference” in our careers.

Thank you, Ewald! This is a great topic!

Here is my short list:

Paulo Silva (in)

He was my first boss in the IT world! I was his apprentice when I started my internship. He was going to move to a manager position and I  had to continue his work. He was responsible for the beginning of my career with SQL Server 2000 and VB6.

He was one of the main culprits for my growth not only in IT but also as a person!

Etienne Lopes (t | b)

After 5 years working on IT I had the tremendous pleasure to meet Etienne. This guy is a professor! He has the gift of the word!

I have worked closely with him for about 2 years and were one of the best times of my career! I always consider myself as a sponge, and as long Etienne shared is knowledge I felt I was absorbing every single word!

Much of the bases I have with SQL Server I learned from him!

André Batista (t) / Niko Neugebauer (t | b)

These two guys are the responsible for my very first talk on a user group (SQLPort).

After that, I became more and more involved with the local community and today I speak for more user groups and I help with SQL Saturday / TugaIT events in Lisbon!

Rob Sewell (t | b)

The one and only DBAWithABeard! My recent experiments were from blog posts/presentations that I read/saw from Rob. PowerBI & Pester are just two of them. He is super accessible and always willing to help.

Chrissy LeMaire (t | b)

I met Chrissy less than 2 years ago at the TugaIT conference (May 2016) in Lisbon. At the time has passed like 1 month from the dbatools.io launch date and I had written a couple of PowerShell scripts that I thought would be nice to add to the initial tool.

We talked, exchange contacts and one month later, in June, I was submitting my first pull request to the dbatools GitHub repository.

From that time until now it has been a blast! I learned so much about PowerShell with her and she is also one of the responsible for my MVP not only because she nominated me for the very first time but also because all the visibility that the project brought to me.

She was also the first person delivering a presentation with me. 🙂

People I know from the magazines or internet

People that helped me to understand SQL Server much better and from whom I have read a lot of articles: Itzik Ben-Gan (I remember the times I read the SQL Magazine with great articles from him), Paul Randal, Kimberly L. Tripp, Adam MachanicPaul White and Kendra Little.

Wrap up

I could add more people to the list but, those are the ones that I want to highlight from different periods (the beginning, middle and nowadays) of my career.

Thank you all!

 

PowerShell Modules Central – Share with community – What PowerShell modules are you using?

Like the blog post title states this is all about sharing with others! My idea is to share with the community which PowerShell modules you are using.

Let me introduce to you the PowerShell Modules Central

PowerShellModulesCentral is a GitHub repository that was founded as a central hub to a list of PowerShell modules that people know/use. Each module has a file describing its name, basic information about the module, as well as one or more blog posts/videos from people that have written about or used them.

This way we can reduce friction when people are starting out or are trying to solve similar problems.

Why?

When a new module appears on the PowerShell scene it can be difficult to advertise and gain mindshare among developers/end users who could be interested in it. There are also times when difficulties arise in finding if a good tool exists or not, if its up to date, and how relevant it is in the community.

Why not just use the PS Gallery or script center?

This is, by no means, a replacement of those. Actually it is opposite, it is meant to be a community complement. Normally, when you need to do a task that you’ve never done before you like to have some jump start like blog posts or videos, and maybe you find the ones that are very close to your real scenario.
This repository enables not only people to write blog posts and share them with the community, but also the new guy (on PowerShell or just on a new task) that is searching for a specific tool to accomplish a task.
I can go to the PowerShell Gallery and see that the module I want to use has 1K downloads. That is really cool! It will give me confidence to use it. But, next, when you want to start working with it maybe you would like to see examples. The objective here is to have a quick look on some problems and tools used to solve them, as they can also be your problem.

Let me tell you a quick story

I went to google, found a PowerShell Gallery script, and after checking that the script didn’t work with some particulars I did a further research and found (google results – page 3 or 4 due ranks) a comment on a forum pointing to the GitHub repository. Guess what? The problems I was having were already addressed. 😉

Are you a module owner? Are you writing something new? Do you contribute to a module? Share it! The ones I know and use could be very different from the ones you know and use! Why not share?

How can this help me?

Are you trying to find a module to work on a specific task? Use the search on the top of the repository page and try to find what you need.

  • Working with Windows? Type “Windows”.
  • Working with SQL Server? Type “SQL Server”.
  • Do you know the author’s name? You can search that way too.
  • Have you read a blog post before and just remember one word or the blogger’s name? Type it and see what you find.

How to contribute?

Just fork the repository, add the information and send a pull request (PR). I will merge it once everything is OK.
For new modules please use the template available here. If you find that module already exists, you just need to add your URLs and any other information to be updated, tags that you think may be useful, add something to description, etc.

If you use a module that doesn’t have a blog post and/or videos yet, you can submit a PR anyway so all of the community can know that it exists and maybe someone will write about it!

Follow up

Follow the repository news by clicking on “Watch” button and/or follow @psmcentral Twitter account.

Feel free to share this blog post! The more people we reach, the better!

Thanks for reading.