time and date from nvachar

Want to convert NVarchar to Time and date using update column command in sql server

 CREATE TABLE [dbo].[timeconvert](
[sno] [int] NULL,
[Duration_Load] [nvarchar](7) NULL,
[Duration] [time](7) NULL,
[Estimated_Date_of_Arrival_Load] [nvarchar](10) NULL,
[Estimated_Date_of_Arrival] [date] NULL )

INSERT [dbo].[timeconvert] VALUES  (1, N'03:26', NULL, N'29.01.2019', NULL) ,(2, N'02:45', NULL, N'30.01.2019', NULL),(3, NULL, NULL, N'03.02.2019', NULL),(4, N'10.25', NULL, N'11.02.2019', NULL)

I want to update the time table with time and date datatype accordingly in the next column.

update  dbo.timeconvert set Duration = time(Duration_Load) , Estimated_Date_of_Arrival = date(Estimated_Date_of_Arrival)

Expected result should match the format in the column type

Answer

You can try this:

UPDATE dbo.timeconvert
SET Duration = REPLACE(Duration_Load,'.',':'),
    Estimated_Date_of_Arrival  = SUBSTRING(Estimated_Date_of_Arrival_Load,4,2)+'/'+LEFT(Estimated_Date_of_Arrival_Load,2)+'/'+RIGHT(Estimated_Date_of_Arrival_Load,4)

Advertisement

Answer