格式日期问题

Question

我有以下查询:

SELECT ACCOUNTNUMBER, PROCESSDATE
FROM INVENTORY

结果:

ACCOUNT   PROCESSDATE
5646546   11082021
4654646   11082021

日期格式错误。

注意:我检查了表的设计和PROCESSDATE字段似乎是一个整数。

我试过什么代码:

.-format(PROCESSDATE, 'DD/mm/yyyy') as PROCESSDATE [the result is DD/mm/yyyy in the column)
.-CONVERT(date, CONVERT(varchar(6), PROCESSDATE) + '01') myDate [The result is an error]
.-CONVERT(CHAR(10), PROCESSDATEAS Datetime) as 'MyDateTime' [the result is an error]

期望输出:获取PROCESSDATE字段为MM/dd/yyyy格式。

Answer 1

这是一个可怕的格式。请注意，如果日期小于10，则整数的长度会发生变化。Arggh !

因此，我的建议是转换为8个字符的字符串(如果有必要，使用前导'0')，然后构造一个规范日期字符串('YYYYMMDD')。并转换为日期:

select convert(date,
right(format(processdate, '00000000'), 4) + substring(format(processdate, '00000000'), 3, 2) +left(format(processdate, '00000000'), 2)
)

实际上可以将format()移动到子查询、CTE或values子句中，如下所示:

select convert(date, right(v.processdate_str, 4) + substring(v.processdate_str, 3, 2) +left(v.processdate_str, 2))
from inventory i cross apply
(values (format(i.processdate, '00000000'))
) v(processdate_str)

这是一个db<>小提琴

Answer 2

您可以使用DATEFROMPARTS来解析这些部分，然后格式化101以放入MM/DD/YYYY格式。像这样

SELECT ACCOUNTNUMBER, 
PROCESSDATE,
calc.dt,
convert(varchar(12), calc.dt, 101) vc_with_date_format
FROM (values (5646546, 11082021),
(5646546, 11082021))
INVENTORY(ACCOUNTNUMBER, PROCESSDATE)
cross apply (values (datefromparts(right(PROCESSDATE, 4),
substring(cast(PROCESSDATE as char(8)),3,2),
left(PROCESSDATE, 2)))) calc(dt);

[EDIT]不包含虚拟表

SELECT i.ACCOUNTNUMBER, 
i.PROCESSDATE,
calc.dt,
convert(varchar(12), calc.dt, 101) vc_with_date_format
FROM INVENTORY i
cross apply (values (datefromparts(right(i.PROCESSDATE, 4),
substring(cast(i.PROCESSDATE as char(8)),3,2),
left(i.PROCESSDATE, 2)))) calc(dt);

ACCOUNTNUMBER   PROCESSDATE dt          vc_with_date_format
5646546         11082021    2021-08-11  08/11/2021
5646546         11082021    2021-08-11  08/11/2021

Answer 3

我认为您的日期在DB中存储为ddMMyyyy。您可以通过应用以下转换获得所需的结果:

CONVERT(VARCHAR, CONVERT(DATETIME,STUFF(STUFF(PROCESSDATE, 5, 0, '/'), 3, 0, '/'),103), 101)