Hi,
I am having issues with dates on different servers and wondering if anyone can help.
I use the following to get my dates in the format yyyymmdd
(DT_STR,4,1252)DATEPART("yyyy",(DT_DATE)MaturityDate) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("m",(DT_DATE)MaturityDate),2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("d",(DT_DATE)MaturityDate),2)
it comes out correctly on the server i develop on
but on the server the data run into the system it comes out the other way for some dates
so as you can see the dates comes out 202-07-01 but on the live server it comes out 2020-01-07
any ideas how to fix this
Dates are not stored in their string representation in the database - they are stored in a datetime or date data type. The problem you are having is defined by how you are parsing the data in SSIS and sending it to the server.
You are parsing the data into a string as YYYY-MM-DD and sending that string to the destination system.
In the first system it appears to be setup to use US English - and the second system appears to be British English...so your formatted string appears to be 2020-07-01 which on the British English system is interpreted as YYYY-DD-MM.
To avoid this issue you need to use a string that is unambiguous like YYYYMMDD or you need to convert the string to an actual date in SSIS - which isn't actually needed in your case because the MaturityDate field is already defined as a date and should just be sent through without performing any data conversion.