Post reply

SQL Server is changing the date format

  • April 25, 2021 at 11:20 pm

    #3875637

    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.

    US Datesdates

    Thanks

  • April 26, 2021 at 7:59 am

    #3875737

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • April 26, 2021 at 3:18 pm

    #3875895

    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.

     

  • April 26, 2021 at 4:35 pm

    #3875935

    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