CODEWITHSUNDEEP
sqlsql-servert-sqldatedatetime
Menzi
Menzi

Reputation: 365

Date format value not updating from previous format to new format after update

When I update a column from a table with a date format of MMM DD,YYYY to a new format. The format doesn't change to the desired format which is YYYY-MM-DD HH:MM:SS. Even when update different value like GETDATE(). The date will change but the format will remain the same.

Current value & format from column the type is varchar

DueDate

Jun 27 2020 12:00AM

Desired format DueDate

2020-06-27 00:00:00.000

Update statement 

update TableName
  set  
  DueDate =  CAST([DueDate] AS smalldatetime),     
  LastSyncDateTime = GETDATE()
where CaseGuid =  'DA2CE6A1-0394-463E-8E8D-962F3A24ADC8'

Upvotes: 0

Views: 1407

Answers (2)

Menzi
Menzi

Reputation: 365

Solution use the format function 

update TableName
  set  
  DueDate =  FORMAT (CAST([DueDate] AS smalldatetime),'yyyy-MM-dd HH:mm:ss'),
  LastSyncDateTime = GETDATE()
  where CaseGuid =  'DA2CE6A1-0394-463E-8E8D-962F3A24ADC8'

https://www.sqlshack.com/a-comprehensive-guide-to-the-sql-format-function/

Upvotes: 0

Ronen Ariely
Ronen Ariely

Reputation: 2434

There is a huge confusion between "Date displaying format" and "Date storing format". The VERY short explanation is that what you mentioned is only a client side displaying format, while SQL Server have specific format which is used for storing dates (remember that the server stores zero and one only).

You can insert dates to a table using different styles (the official name for the displaying format is STYLE), and you can present the dates in the client side using different style, but it will always be stored the same from the "SQL Server point of view" according to the DATE type which is used.

In order to solve your original needs, all that you needed to do is to provide the server the information about the style which you use in the client side (in the query). This is done by using explicit CONVERT with the third parameter, which is the STYLE.

For example if you use in the client side an Israeli format like dd/MM/yyyy, then you should use CONVERT(DATE, '27/02/2021', 103).

For more information on different STYLEs you can check this documentation.

Note: If you want to display the dates in specific format which is not covered by the existing STYLEs then you can use the function FORMAT() in your query. This function is fully flexible to return the data in your specific format. Remember that this function returns the data as string and it will not be date anymore.

For example, let's say that I want to use the format: "Day:dd,Month:MM,Year:yyyy". So if the date is '27/02/2021' then I expect to get "Day:27,Month:02,Year:2021". In this case use below:

DECLARE @D DATE
SET @D = CONVERT(DATE, '27/02/2021', 103) -- convert string to date for storing 
select FORMAT(@D, 'Day:dd, Month:MM, Year:yyyy') -- convert date to string for displaying

Upvotes: 1

Related Questions