For one the production system, different date/time format I am getting data time like 11.22.2022 09.10.00 and when try to run below sql query I am getting error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value,

I tried to convert date before querying SQL like '{ac.LastCollectionTime:MM/dd/yyyy HH:mm:ss}'),"), but it's not converting.

DECLARE @data TABLE(Id uniqueidentifier, Value DateTime) 
INSERT INTO @data VALUES ('57f5a153-3cce-48d4-aa0d-894c14b1a2ab', '11.22.2022 09.10.00')
UPDATE A SET LastCollectionTime = AD.Value
FROM App A INNER JOIN @data AD on AD.Id = A.Id

How to fix this issue?

CodePudding user response：

As this is not a date string format SQL Server is aware of out the box, you will either need to adjust your data source to provide an appropriate format or parse out the bits you need manually.

One such way of doing this is as follows:

create table d(ds varchar(25));
insert into d values
 ('11.22.2022 09.10.00')
,('11.01.2022 01.01.01');


select d.ds
      ,datetime2fromparts(dp.y,dp.mo,dp.d,dp.h,dp.mi,dp.s,0,0) as dt
from d
  cross apply(values(substring(d.ds,7,4)
                    ,substring(d.ds,1,2)
                    ,substring(d.ds,4,2)
                    ,substring(d.ds,12,2)
                    ,substring(d.ds,15,2)
                    ,substring(d.ds,18,2)
                    )
             ) as dp(y,mo,d,h,mi,s)

Which outputs: