sql-server - 将 float 转换为日期时间

Question

我有一个日期的 FLOAT 字段，如下所示:

+-----------+
| FloatDate |
+-----------+
| 1012013   | -- Jan 1
| 3262013   | -- Mar 26
| 11072013  | -- Nov 7
| 10012013  | -- Oct 1
+-----------+

我想把这个数据插入到另一个表中，它的dateField的数据类型是DATETIME。

所以，我正在尝试将 FloatDate 值转换为 DATETIME，但我一直在失败......

我试过了

INSERT INTO NEWTABLE 
(DateTimeDate)
SELECT CONVERT(DATETIME, CAST(CAST(FloatDate AS INTEGER) AS CHAR), 112)
FROM OLDTABLE

但这给了我一个错误，说

Conversion failed when converting datetime from character string

如果FloatDate的日期格式是YYYYMMDD的话好像可以。所以，如果我这样做，它就会起作用。

DECLARE @test FLOAT
SET @test = 2132013                                                                                                                            
SELECT @test,  CONVERT(DATETIME, CAST(RIGHT(CAST(@test AS INTEGER), 4) AS CHAR(4)) + 
CASE WHEN LEN(CAST(@test AS INTEGER)) = 7 THEN '0' + CAST(LEFT(CAST(@test AS INTEGER), 3) AS CHAR(3))
     WHEN LEN(CAST(@test AS INTEGER)) = 8 THEN CAST(LEFT(CAST(@test AS INTEGER), 4) AS CHAR(4)) END, 1)

但是，我想知道我是不是把它弄得太复杂了，只是错过了更简单的解决方案，或者这是我能实现的唯一方法吗？

Answer 1

假设您有其他值，例如:

 3092013 -- March 9th
10052013 -- October 5th
 1112013 -- January 11th
11012013 -- November 1st

然后你可以这样做:

DECLARE @x TABLE(FloatDate FLOAT);

INSERT @x VALUES
( 3092013),-- -- March 9th
(10052013),-- -- October 5th
( 1112013),-- -- January 11th
(11012013);-- -- November 1st

;WITH s(d) AS (SELECT RIGHT('0' + CONVERT(VARCHAR(32), 
  CONVERT(INT, FloatDate)), 8) FROM @x),
d(d) AS (SELECT CONVERT(DATETIME, 
  RIGHT(d,4) + LEFT(d,2) + SUBSTRING(d,3,2)) FROM s)
SELECT d FROM d;

结果:

d
-----------------------
2013-03-09 00:00:00.000
2013-10-05 00:00:00.000
2013-01-11 00:00:00.000
2013-11-01 00:00:00.000

在您的示例中，它将是:

;WITH s(d) AS (SELECT RIGHT('0' + CONVERT(VARCHAR(32), 
  CONVERT(INT, FloatDate)), 8) FROM dbo.OLDTABLE),
d(d) AS (SELECT CONVERT(DATETIME, 
  RIGHT(d,4) + LEFT(d,2) + SUBSTRING(d,3,2)) FROM s)
INSERT dbo.NEWTABLE
SELECT d FROM d;

当然，即使您获得了有效 值的正确语法，您也可能在那里有一些错误数据。由于您使用了 FLOAT，您没有得到任何固有的验证，因此该列可能包含 -999 或 45671213 等。

你觉得这很痛苦吗？好的!它应该是痛苦的。您应该回到决定将日期存储在 FLOAT 列中的人那里，然后踢他们的小腿。询问他们是否使用地理数据类型存储薪水。我很高兴您正在解决此问题，但不应允许这些人参与数据库设计。

最后，请不要这样做:

CAST(anything AS CHAR)

总是指定一个长度。原因如下:

• Bad habits to kick : declaring VARCHAR without (length)