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

, 2018-09-12

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!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads