date still showing up as YYYY-MM-DD

Question

Post reply

date still showing up as YYYY-MM-DD

winston Smith

SSCoach

Points: 19498
More actions
August 26, 2014 at 5:51 am

#298858

Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but the date is still YYYY-MM-DD.
Any ideas how i can change this?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233841 More actions · Answer 1

Please post the result of this query

select configuration_id, value

from sys.configurations

where configuration_id = 124

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

HowardW One Orange Chip Points: 29109 More actions · Answer 2

The display format of dates is dictated by the client, not the server. A date is a type that has no specific display format.

SSMS, for example, is a client of SQL Server and has its own display rules within the grid view. I'm not sure if you can change this. In most client applications/code, you can set the formatting of dates ...

The server-side language settings tend to control the date formats that can be used for implicit conversion when referring to a date in T-SQL

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

winston Smith (8/26/2014)
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but the date is still YYYY-MM-DD.
Any ideas how i can change this?

The date where? From what? You need to be more specific, as far as we know you could be talking about the calendar on your desk. What does SELECT GETDATE() return (to a results pane in SSMS)? What do you expect it to return if it's not what you are expecting? Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

winston Smith SSCoach Points: 19498 More actions · Answer 4

ChrisM@Work (8/26/2014)
winston Smith (8/26/2014)
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but the date is still YYYY-MM-DD.
Any ideas how i can change this?
The date where? From what? You need to be more specific, as far as we know you could be talking about the calendar on your desk. What does SELECT GETDATE() return (to a results pane in SSMS)? What do you expect it to return if it's not what you are expecting? Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.

Is a sql server forum, so its probably safe to figure that Im looking at the date format within sql server!

When i select getdate() it returns yyyy-mm-dd

When i import a flat file of data into a table, all date fields are in the format of YYYY-MM-DD, despite being correct in the flat file.

winston Smith SSCoach Points: 19498 More actions · Answer 5

Perry Whittle (8/26/2014)
Please post the result of this query
select configuration_id, value
from sys.configurations
where configuration_id = 124

Result of that query is:

ConfigurationID value

124 23

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

winston Smith (8/26/2014)
ChrisM@Work (8/26/2014)
winston Smith (8/26/2014)
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but the date is still YYYY-MM-DD.
Any ideas how i can change this?
The date where? From what? You need to be more specific, as far as we know you could be talking about the calendar on your desk. What does SELECT GETDATE() return (to a results pane in SSMS)? What do you expect it to return if it's not what you are expecting? Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.
Is a sql server forum, so its probably safe to figure that Im looking at the date format within sql server!
When i select getdate() it returns yyyy-mm-dd
When i import a flat file of data into a table, all date fields are in the format of YYYY-MM-DD, despite being correct in the flat file.

The rest of the question?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

winston Smith SSCoach Points: 19498 More actions · Answer 7

Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.

if i could find that, i wouldnt have needed to post here asking the question!

What I am sure of is that I have used SQL Server instances plenty of times that return/store dates in dd-mm-yyyy, yet despite my efforts this server insists on using the format yyyy-mm-dd.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

winston Smith (8/26/2014)
Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.
if i could find that, i wouldnt have needed to post here asking the question!
What I am sure of is that I have used SQL Server instances plenty of times that return/store dates in dd-mm-yyyy, yet despite my efforts this server insists on using the format yyyy-mm-dd.

SQL Server stores datetimes in a numeric format, the first part is the number of days since 19000101 and the second part is the elapsed part of the day as a decimal fraction. Regardless of your regional settings.

SQL Server uses regional settings for display. I'm working on a UK-centric instance right now using a UK-centric client. If I issue SELECT GETDATE() in an SSMS window, it returns exactly what I expect to see: YYYY-MM-DD. You can use CONVERT to output pretty much any date/time format you like, but raw, it's as you see it right now.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Perry Whittle SSC Guru Points: 233841 More actions · Answer 9

what are the Windows regional settings on the server set to

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

winston Smith SSCoach Points: 19498 More actions · Answer 10

Formats are set to:

shortDate: dd/MM/yyyy

LongDate: dd MMMM yyyy

Location is Ireland

Language for non unicode programs is English(Ireland)

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 11

winston Smith (8/26/2014)
Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.
if i could find that, i wouldnt have needed to post here asking the question!
What I am sure of is that I have used SQL Server instances plenty of times that return/store dates in dd-mm-yyyy, yet despite my efforts this server insists on using the format yyyy-mm-dd.

Are you sure that your SSMS is not formatting the date value for you?

This is how the date looks on all my North American servers in SSMS, no matter they be Canadian or US.

Other clients and applications work of the regional settings. This was a per system setting.

Then with windows XP it became a per user setting. Starting with windows Vista each application can have it's one regional settings it can use.

Check all three since that is what determines what you see on your screen.

Any more than that and I would need to see some create database and create table statements with the "Malfunctioning" date field statement highlighted.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 12

I agree with PHYData DBA. This should be a setting in Windows that SSMS picks up. I would guess that any rendering by a client, regardless of app, should also follow this rule.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 13

Steve Jones - SSC Editor (8/27/2014)
I agree with PHYData DBA. This should be a setting in Windows that SSMS picks up. I would guess that any rendering by a client, regardless of app, should also follow this rule.

Hey Steve! Aren't you on Sebaticle?! Stop Working!! lol...

But seriously I worked on a global app that had custom date controls. When we went Global with the app (i.e. Canada, UK, European Union) the Date controls would not work correctly in Canada and Europe. They where created to validate dates where entered in the mm/dd/yyyy format, which because of the regional date setting, meant the date was going back to the server as an invalid date from the Canadian and Europe systems.

However my SSMS then and now always showed things as YYYY-MM-DD. It is like that by default.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 14

I've been back for a month 🙂

I think the defaults change by versions, but certainly I would hope that yyyy-mm-dd would always be used. Be nice to get people used to that and prevent confusion.