Question

SQL 数据类型转换 日期由数字型numeric(18, 0)转换为datetime型

我知道numeric(18, 0)是18位有效数字的数字型，如201307；
datetime型是年月日：时分秒，如：2013-7-11 12:38:01。

我现在的问题是如何将数值型201307，转变成datetime型的2013-7-1:00:00:00。
数据是有点不全，数字型的数据只给到“月”，后面的“日”、“小时”、“分”、“秒”什么的都没有。但是可以“默认”，即只精确到“月”就可，后面的可以随便填。

我要做一个按月来查询的查询功能，而且必须是datetime型的日期，但他们给我的数据库里的日期却是numeric(18, 0)。
下面是数据截图：
这个是numeric(18, 0)型日期，

这个是他的数据类型截图

这是datetime型
我试过直接在“修改”里面把numeric(18, 0)改为datetime型，能改，但是数据变了：

我也在百度上找了很久，都没找到有效方法。速求解决，已给最高悬赏。
我会每半个小时过来看一次。

可别跟我说手动一个一个去改，数据太多了，如果手动改的话，要改到何年何月啊。





Answer 1

第一步：你做错了，他们给的这个数据类型根据你的需求根本用不着更改为datetime，为什么会变成这样也是因为这个数据根本就不能转成datetime模式
/* 接下来。。。。。重新恢复你的数据类型为 numeric(18, 0) 看数据能不能恢复原来的样子 或着用下面二位的用法，再根据year和month取值,我建议先试前面一种(因为这是人家给你的数据，不能乱改，万一人家在其它地方有引用，人家问起来你就不好回了) */

前面一种按年月来查询可以这样写
select left(fyearmonth,4),right(left,2),sum(qty) from tb
group by left(fyearmonth,4),right(left,2)

Answer 2

-- 模拟你的 原始表 和原始数据.
CREATE TABLE #test (
  test numeric(18, 0)
);
GO

INSERT INTO #test VALUES('201212')
INSERT INTO #test VALUES('201301')
GO

-- 模拟修改数据类型.
ALTER TABLE #test ALTER COLUMN test DATETIME;
GO

-- 查询数据 （应该是不正确的）
SELECT * FROM #test 
GO

test
-----------------------
2450-11-25 00:00:00.000
2451-02-22 00:00:00.000

(2 行受影响)


-- 修正数据之前的查询处理.
SELECT
  DATEADD(mm,  (DATEDIFF(dd,  '1900-01-01', test) % 100) -1,
    DATEADD(yy, DATEDIFF(dd,  '1900-01-01', test) / 100 - 1900, 0))
FROM
  #test;
GO

-----------------------
2012-12-01 00:00:00.000
2013-01-01 00:00:00.000

(2 行受影响)


-- 满足预期， 正式作更新处理.
UPDATE
  #test
SET
  test = DATEADD(mm,  (DATEDIFF(dd,  '1900-01-01', test) % 100) -1,
    DATEADD(yy, DATEDIFF(dd,  '1900-01-01', test) / 100 - 1900, 0));
GO

-- 数据核对.
SELECT * FROM #test;
GO

test
-----------------------
2012-12-01 00:00:00.000
2013-01-01 00:00:00.000

(2 行受影响)

Answer 3

先将数据类型改成varchar，然后将所有数据拼接01，update
最后将数据类型修改为datetime，用varchar类型做一个中间转接

追问

然后将所有数据拼接01，update？？
怎么做啊 不懂。。

追答

update tb set 字段=字段+'01'就是字符拼接喽，然后再修改数据类型
alter table tb
alter column tb datetime

Answer 4

1。你先新建一列名字叫FYearMonth2 , datetime的
2.执行
update tablename
set FYearMonth2=convert(datetime,
left(cast(fyearmonth as varchar(6)),4) + '-' + right(cast(fyearmonth as varchar(6)),2) + '-01 00:00:00',120)

3. 删除原来叫FYearMonth的列
4. 把FYearMonth2改为FYearMonth

追问

这么多大神啊 不过你这种方法他先提出。。。

Answer 5

select convert(datetime,
left(cast(fyearmonth as varchar(6)),4) + '-' + right(cast(fyearmonth as varchar(6)),2) + '-01 00:00:00',120)
from xxxx

追问

你这个是查询语句啊，没有改变表结构。。。

追答

首先，你在表里先加一列： fyearmonth2 datetime类型

接着，用我上面的语法，update fyearmonth2列
update xxxx set fyearmonth2=convert(datetime,
left(cast(fyearmonth as varchar(6)),4) + '-' + right(cast(fyearmonth as varchar(6)),2) + '-01 00:00:00',120)

然后，你删除掉 fyearmonth 列

最后，将 fyearmonth2 列名改为 fyearmonth

追问

这个方法可行。刚有事出去了下，所以到现在才回来看到。

试验过，OK。

 

再问下，如果我要只显示年月，SQL与怎么写？

如2013-1-1 0:00:00，显示成201301

 

 

我去，我明明是想把你的回答作为最佳答案的。。。分数我在另外一个问题上补偿你http://zhidao.baidu.com/question/569132962.html

追答

要显示成201301的样子，

必须在查询时转换
假设fyearmonth 已经是datetime类型

select convert(varchar(6),fyearmonth,120) from xxx