SQL Server how to convert string '03-January-2023' to datetime on insert

6ss1mwsb 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(111)

I have a table into which I want to convert a string to datetime and insert. However I am getting a conversion failed error

INSERT INTO CUSTOMERS_PURCHASE([PURCHASE_DATE], [PURCHASE_TIME], [PURCHASE_CITY]) 
SELECT CAST( '03-January-2023' AS DATETIME2) AS PURCHASE_DATE,  
[PURCHASE TIME] AS PURCHASED_TIME, [PURCHASE_CITY] FROM CUSTOMERS_INDIA

Error: Conversion failed when converting date and/or time from character string.

Can I get some advice/ help on how I can resolve this? I am using Microsoft SQL

sql-server

来源：https://stackoverflow.com/questions/76441914/how-to-convert-string-03-january-2023-to-datetime-on-insert

1条答案

按热度按时间

vwkv1x7d1#

Use try_convert() ... It will return a NULL rather than throwing an error if the conversion fails

Declare @YourTable Table ([ID] varchar(50),SomeCol varchar(50))  Insert Into @YourTable Values 
 (1,'03-january-2023')
,(2,'03 jan 2023')
,(3,'03 ZZZ 2023')
,(4,'Just a a string')
 

Select ID
      ,NewValue = try_convert(datetime,SomeCol)
 from @YourTable

Results

ID  NewValue
1   2023-01-03 00:00:00.000
2   2023-01-03 00:00:00.000
3   NULL
4   NULL

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server how to convert string '03-January-2023' to datetime on insert

1条答案

相关问题

热门标签

最新问答