0

I don't have control over our servers running SQL Server and I'm told to expect the date settings to be set to my local time zone, which happens to be UK.

Some of them are set to USA date format, which convert dates as month day year, instead of day month year.

Does anyone have a good way to test all the date settings of all my servers?

I was thinking of having a procedure called, 'OneSlashThreeSlashTwentySeventeenMonth', which exists on each server. This procedure will say which Month it thinks, '1/3/2017' represents.

It could group them together too into servers that report March and those that report January.

Note: This question assumes that the date settings are set at server level, and not database level. If they can be set at database level then it would make more sense for me to test every database on every server.

Having something I can run means that I can forward it to the people that look after our servers and request the settings be changed en masse.

All help gratefully received.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
  • I'm not precisely sure what you're trying to accomplish but maybe you should look into utilizing DAY(), MONTH() and YEAR() functions? Make a procedure on each server which gives you back the year, the month and the day within the month as separate columns? This way there's no confusion as to which is which. – MK_ Mar 09 '17 at 08:29
  • Why? Date formats are for display only. [Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) Either use datetime or `mm-dd-yyyyThh:mm:ss.nnn` strings for inserting datetime values, and when selecting them, either select datetime values or use convert to select the string format you want. – Zohar Peled Mar 09 '17 at 08:53

2 Answers2

0

This query will return the order of date parts:

SELECT [dateformat] FROM sys.syslanguages WHERE [langid]=@@LANGID

Just have a look into sys.syslanguages to find more details!

But to be honest: This should not be necessary. If this is an old issue (dealing with legacy systems) it might be needed, but in general you should stick to culture / language independant formats, whenever you have to pass a datetime as string. If ever possible stick to a typed value which doesn't care about the format.

Here's a related answer

And here's another one

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

I was thinking of having a procedure called, 'OneSlashThreeSlashTwentySeventeenMonth', which exists on each server. This procedure will say which Month it thinks, '1/3/2017' represents.

It could group them together too into servers that report March and those that report January.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
  • The whole approach smells... Did you check my answer? Why do you want to do something such incredibly complicated? My answer will return `dmy` or `mdy` or `ymd` telling you immediately, how the default cast will interpret your strings. But - as told in my answer - you are solving a problem you really should not have at all! – Shnugo Mar 09 '17 at 10:54
  • The situation smells alright. The solution is a report to a) convince the people in charge of the servers to investigate the date settings, b) aid them solve the issue asap. and c) reassure myself if they update the settings silently and deny all knowledge. Thing is, we employ Excel based reports where dates are supplied as a UK formatted string, so the date settings of the server dictate whether, '1/3/2017' is interpretted as 'March 1st' or 'January 3rd'. SET DATEFORMAT DMY within the proc doesn't help when a string is passed as a date parameter. – WonderWorker Mar 09 '17 at 16:42