Search code examples
sql-serverdatedate-formatswap

How to swap day and month SQL Server

I have a column called reading_date and it is defined as datetime. I have many records in it and I could figure out that there are many records where the day is in the month and the vise versa. For e.g 2019-05-01 which it should be 2019-01-05. is there any way to swap and fix such issue. I am using SQL Server. Thanks in advance

Solution

  • Split dates using DATEPART function and gather them in another way using DATEFROMPARTS.

    UPDATE yourtable 
SET yourdate = 
  DATEFROMPARTS(
    DATEPART(year, [yourdate]),
    DATEPART(day, [yourdate]),  
    DATEPART(month, [yourdate]))
WHERE ...