SQL Server How can I convert numeric(16) formatted as yyyyMMddHHmmss00 to Datetime in SQL [duplicate]

b1zrtrql 于 2023-04-04 发布在其他

关注(0)|答案(2)|浏览(169)

This question already has answers here:

Convert integer data type to time (2 answers)
Converting numeric(17,9) into a datetime (3 answers)

Closed 5 days ago.

I want to convert start_date numeric(16) to datetime

for example:
2023032012121201 to Datetime

I tried this

SELECT CONVERT(datetime, SUBSTRING(CAST(start_date AS varchar(16)), 1, 8) + ' ' + 
      STUFF(STUFF(STUFF(RIGHT('0000000000' 
      + CAST(start_date AS varchar(16)), 10), 3, 0, ':'), 6, 0, ':'), 9, 0, '.')) 
 FROM table

but it gives Conversion failed when converting date and/or time from character string error

Output should be: 2023-03-20T12:12:12.01

sql-server

来源：https://stackoverflow.com/questions/75877696/how-can-i-convert-numeric16-formatted-as-yyyymmddhhmmss00-to-datetime-in-sql

2条答案

按热度按时间

b4lqfgs41#

If the value is a numeric(16,0) (or a varchar(16) ), then you can inject the needed characters ( `` , : and . ) into the needed positions and then TRY_CONVERT that to a datetime2(2) . This turns the value into the format yyyyMMdd hh:mm:ss.nn which is an unambiguous date and time format in SQL Server:

DECLARE @YourString numeric(16,0) = '2023032012121201';
SELECT @YourString,
       TRY_CONVERT(datetime2(2),STUFF(STUFF(STUFF(STUFF(@YourString,15,0,'.'),13,0,':'),11,0,':'),9,0,' '));

I use TRY_CONVERT because you might have bad data; storing date and time values in anything other than a date and time data type throws validation out the window. As such any such values will return NULL instead. Likely you will also want to find and correct such values before you fix your design and change the data type to a date and time data type ( datetime2(2) seems the right choice here, and why I used it).

赞(0）回复(0）举报 2023-04-04

2izufjch2#

Another possibility is

CREATE tABLe t1(start_date numeric(16) )

INSERT INTO t1 VALUES (2023032012121201 )

SELECT CAST(SUBSTRING(CAST(start_date AS varchar(16)), 1, 8) + ' ' 
  + SUBSTRING(CAST(start_date AS varchar(16)), 9, 2) + ':'
  + SUBSTRING(CAST(start_date AS varchar(16)), 11, 2) + ':'
  + SUBSTRING(CAST(start_date AS varchar(16)), 13, 2) + '.' 
  + SUBSTRING(CAST(start_date AS varchar(16)), 15, 2) AS datetime)  FROM t1

(No column name)
2023-03-20 12:12:12.010

fiddle

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server How can I convert numeric(16) formatted as yyyyMMddHHmmss00 to Datetime in SQL [duplicate]

2条答案

相关问题

热门标签

最新问答