question

RobertJubb-3334 avatar image
0 Votes"
RobertJubb-3334 asked EchoLiu-msft commented

Change date format in sql table

Hi there,

We currently have a table column where the datatype is NVARCHAR(15), which is used to store a date in the format 16/04/2021. We would like to be able to change that format of the existing rows in the table to 20210416.

Is this possible?

Any guidance would be appreciated

Thank you

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you provide any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

0 Votes 0 ·
DanGuzman avatar image
0 Votes"
DanGuzman answered DanGuzman edited

If all the date strings are valid d/m/y dates, the query below will do the job. You'll need to add TRY_CAST or TRY_CONVERT if you have existing bad date values to avoid a conversion error, and remediate the invalid values as desired.

 SET DATEFORMAT dmy;
 UPDATE dbo.YourTable
 SET ThisColumnShouldBeDateDataType = CONVERT(char(8), CAST(YourColumnn AS date), 112);

As suggested by the column name in the example query, it is best to store data with the proper data type and control display formatting in the application. This will help performance, reduce storage requirements, and improve data integrity.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

If you cannot yet switch to appropriate types, the check this statement:

 update MyTable
 set MyColumn = concat(right(MyColumn, 4), substring(MyColumn, 4, 2), left(MyColumn, 2))
 where MyColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @RobertJubb-3334

Please refer to:

     CREATE TABLE #yourtable(str varchar(15))   
     INSERT INTO #yourtable VALUES('16/04/2021')   
            
     UPDATE #yourtable   
     SET str=FORMAT(CONVERT (date,str,103),'yyyyMMdd')   
           
     SELECT str FROM #yourtable   

Output:

     str   
     20210416   

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.