SQL Server is changing the date format
-
Hi All,
I am using VS 2017 to import data from excel to a SQL table, very basic no problems with loading the rows. The issue is I need the date format to be UK dd/mm/yyyy and SQL server keeps changing the formatting to US once it hits the table.
The windows clock on the server has UK format and I am using a derived column editor; (DT_STR,2,1252)DAY([Start date ]) + "/" + (DT_STR,2,1252)MONTH([Start date ]) + "/" + (DT_STR,4,1252)YEAR([Start date ]) to get date format I need. The SSIS data viewer right before the table has the columns in UK format but then the table has US format.
So I'm not sure what can be done to fix this.
Thanks
-
Assuming your target column's datatype is 'DATE', it does not have a display format.
If your import is converting formats from DDMM to MMDD, you would get lots of errors for all of the invalid dates. If you are not getting errors, the problem is likely that you have US format on whatever client machine you are using to SELECT the data after import.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Try setting your Windows date format in Regional Settings, I found that is affects how dates are presented in various applications. How to get there depends on your Windows version. In 1909, it's in Control Panel > Region.
Set your Short date and Long date formats to how you would like them to appear, and see if that helps.
-
Also language setting on the login to the server too.
each sql based login/group will have a language format that will have impacts to dmy mdy settings
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply