September 30, 2021 at 12:43 pm
Ahoi,
we migrated from SQL Server 2016 to 2019 on a new server.
I currently have a conversion or problem with dates.
Those are the things ive tried/settings:
--With and without USE GUE_BI;
USE GUE_BI ;
GOEXEC sp_configure 'default language', 23 ;
GORECONFIGURE ;GO
It changes the Settings in SSMS but not in the queries.
Even after disconnecting and reconnecting to the instance i still have the following problem:
--Returns: us_english
SELECT @@LANGUAGE;
--Returns 1 instead of 10
select month(cast('01.10.2020' as date))
DBCC Works but only in the current window and is not of help since i need it for all users (including sys users/service users/..)
DBCC USEROPTIONS;
SET DATEFORMAT DMY --MDY
How to i set DMY the default for the whole instance so that it actually works/influences the date format of all queries (10 instead of 1):
select month(cast('01.10.2020' as date))
I want to be the very best
Like no one ever was
September 30, 2021 at 2:42 pm
Set logins default language to British English instead of English.
Or as you should be doing, use ISO formats for dates and always provide them in a YYYY-MM-DDTHH:mm:SS.MS format
October 1, 2021 at 4:17 am
Set logins default language to British English instead of English.
Or as you should be doing, use ISO formats for dates and always provide them in a YYYY-MM-DDTHH:mm:SS.MS format
Thanks but the ISO format solution does not help if i have hundres of tables of the original data source that present different date formats right from the get go or am i wrong?
Gotta check if i can alter ALL the users.. hope it works for all the service users or im fucked....
I want to be the very best
Like no one ever was
October 1, 2021 at 5:37 am
Well dates should be handled as dates not strings so the most important rule, store them in datetime columns not varchar/nvarchar columns.
SQL will then store the datetime value as a number - or + from the default date so that it knows the iso format when displayed on a select.
Get your people to remember to use ISO formatting if they are defining dates as strings. Help SQL to help you by using standard formats.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply