如何根据日期中的月份判断日期类型？

Question

如果月份为4月的话，类型为01
如果月份为5月的话，类型为02
如果月份为6月的话，类型为03
……
依次类推例如表1：
Date
2009-04-23
2009-05-20
2009-06-12
2008-03-01结果为：
Date             DateType
2009-04-23       01
2009-05-20       02
2009-06-12       03
2008-03-01       12用SQL如何实现？

Answer 1

你可以建立个表呀
4-01
5-02要不就用CASE WHEN 

Answer 2

select 
  date, DateType=case when month(DateType)=4 then '01'
                      when .....
 ........
end
from tb 

Answer 3

select [date],
datetype=
case 
 when month([date])=4 then '01' 
 when month([date])=5 then '02' 
 when month([date])=6 then '03' 
 when month([date])=7 then '04' 
 when month([date])=8 then '05' 
 when month([date])=9 then '06' 
 when month([date])=10 then '07' 
 when month([date])=11 then '08' 
 when month([date])=12 then '09' 
 when month([date])=1 then '10' 
 when month([date])=2 then '11' 
 when month([date])=3 then '12' 
end
from tb

Answer 4

select Date , case when datepart(mm,date) >= 4 then '0'+ltrim(datepart(mm,date)-3) else ltrim(datepart(mm,date) + 9) datetype from tb

Answer 5

create table tb(date datetime)
insert into tb values('2009-04-23')
insert into tb values('2009-05-20')
insert into tb values('2009-06-12')
insert into tb values('2008-03-01')
insert into tb values('2008-01-01')
insert into tb values('2008-02-01')
insert into tb values('2008-07-01')
insert into tb values('2008-08-01')
insert into tb values('2008-09-01')
insert into tb values('2008-10-01')
insert into tb values('2008-11-01')
insert into tb values('2008-12-01')
goselect Date , case when datepart(mm,date) >= 4 then '0'+ltrim(datepart(mm,date)-3) else ltrim(datepart(mm,date) + 9) end datetype from tbdrop table tb/*
Date                                                   datetype      
------------------------------------------------------ ------------- 
2009-04-23 00:00:00.000                                01
2009-05-20 00:00:00.000                                02
2009-06-12 00:00:00.000                                03
2008-03-01 00:00:00.000                                12
2008-01-01 00:00:00.000                                10
2008-02-01 00:00:00.000                                11
2008-07-01 00:00:00.000                                04
2008-08-01 00:00:00.000                                05
2008-09-01 00:00:00.000                                06
2008-10-01 00:00:00.000                                07
2008-11-01 00:00:00.000                                08
2008-12-01 00:00:00.000                                09（所影响的行数为 12 行）
*/

Answer 6

select date, DateType=case when month(Date)=4 then '01' 
                           when month(Date)=5 then '02'
                           when month(Date)=6 then '03'
                           when month(Date)=7 then '04'
                           end from table

Answer 7

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Date] datetime)
insert [TB]
select '2009-04-23' union all
select '2009-05-20' union all
select '2009-06-12' union all
select '2008-03-01'select  [Date]=convert(varchar(10),[Date],120),
[DateType]=right('0000'+cast(abs(datediff(mm,'2009-04-23',[date])+1) as varchar(10)),2)
from [TB]
/*
Date       DateType 
---------- -------- 
2009-04-23 01
2009-05-20 02
2009-06-12 03
2008-03-01 12（所影响的行数为 4 行）*/drop table TB

Answer 8

IF(OBJECT_ID('TEMPDB..#T') IS NOT NULL)
BEGIN
DROP TABLE #T
END
CREATE TABLE #T
(
[DATE] DATETIME
)
INSERT INTO #T
SELECT DATEADD(M,4,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,5,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,6,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,7,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,8,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,9,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,10,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,11,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,12,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,1,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,2,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,3,GETDATE())
SELECT DATEPART(M,[DATE]) OLD
,DATEPART(M,DATEADD(M,-3,[DATE])) NEW
FROM #T ORDER BY OLD------------------------
OLD     NEW
1 10
2 11
3 12
4 1
5 2
6 3
7 4
8 5
9 6
10 7
11 8
12 9

Answer 9

IF(OBJECT_ID('TEMPDB..#T') IS NOT NULL)
BEGIN
DROP TABLE #T
END
CREATE TABLE #T
(
[DATE] DATETIME
)
INSERT INTO #T
SELECT DATEADD(M,4,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,5,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,6,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,7,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,8,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,9,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,10,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,11,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,12,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,1,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,2,GETDATE())
INSERT INTO #T
SELECT DATEADD(M,3,GETDATE())SELECT DATEPART(M,[DATE]) OLD,RIGHT('0000'+CAST(DATEPART(M,DATEADD(M,-3,[DATE])) AS NVARCHAR(100)),2) NEW
FROM #T ORDER BY OLD-----------------
OLD    NEW
1 10
2 11
3 12
4 01
5 02
6 03
7 04
8 05
9 06
10 07
11 08
12 09