cREATE TABLE MT( ID INT , FDATE NVARCHAR(20)) INSERT INTO MT SELECT 1, '01JUN' UNION SELECT 2, '02FEB' UNION SELECT 3, '21JUN' UNION SELECT 4, '03JAN' UNION SELECT 5, '25NOV' UNION SELECT 6, '30DEC' declare @s nvarchar(20) set @S='01JAN' declare @e nvarchar(20) set @e='01JUN'SELECT ID ,FDATE FROM MT where cONVERT(datetime, stuff(FDATE,3,0,' ')+' 1900') between cONVERT(datetime, stuff(@S,3,0,' ')+' 1900') and cONVERT(datetime, stuff(@e,3,0,' ')+' 1900')ID FDATE ----------- -------------------- 1 01JUN 2 02FEB 4 03JAN(3 row(s) affected)
CREATE VIEW dbo.BP_BPDD_BPI AS select BPDD.PRECODE,BPDD.PRODUCTID,BPDD.PRODUCTRELATION1,BPDD.PRODUCTRELATION2,BPDD.EXCHANGEKIND,(BPDD.RATE*BPDD.QVALUE) AS QVALUE,BPDD.TAX,BPDD.SALEPRICE,BPDD.PROFIT,BPDD.REMARK, BP.CUSTOMERNAME,BP.CREATTIME,BPI.Place1,BPI.Place2,BPI.Place3,BPI.Place4, BPI.Place5, BPI.Carrier1 ,BPI.Carrier2,BPI.Carrier3,BPI.Carrier4,BPI.Class1,BPI.Class2,BPI.Class3,BPI.Class4, BPI.Flight1, BPI.Flight2,BPI.Flight3,BPI.Flight4, BPI.Date1,ISNULL(BPI.TourCode,'') AS TOURCODE ,BP.GRUP,BPDD.INPRICE,BPDD.OUTPRICE , CASE SUBSTRING(BPI.DATE1,3,3) WHEN 'JAN' THEN '190001'+substring(date1,1,2) WHEN 'FEB' THEN '190002'+substring(date1,1,2) WHEN 'MAR' THEN '190003'+substring(date1,1,2) WHEN 'APR' THEN '190004'+substring(date1,1,2) WHEN 'MAY' THEN '190005'+substring(date1,1,2) WHEN 'JUN' THEN '190006'+substring(date1,1,2) WHEN 'JUL' THEN '190007'+substring(date1,1,2) WHEN 'AUG' THEN '190008'+substring(date1,1,2) WHEN 'SEP' THEN '190009'+substring(date1,1,2) WHEN 'OCT' THEN '190010'+substring(date1,1,2) WHEN 'NOV' THEN '190011'+substring(date1,1,2) WHEN 'DEC' THEN '190012'+substring(date1,1,2) END AS DATEORDER from BILLPAYPRODUCTDTL BPDD INNER JOIN BILLPAYPRODUCT BP ON BP.BILLNO=BPDD.BILLNO INNER JOIN BillPayITicket BPI ON BPI.BOOKPID=BPDD.BOOKPID AND LEN(BPI.DATE1)=5 UNION ALL select BPDD.PRECODE,BPDD.PRODUCTID,BPDD.PRODUCTRELATION1,BPDD.PRODUCTRELATION2,BPDD.EXCHANGEKIND,(BPDD.RATE*BPDD.QVALUE) AS QVALUE,BPDD.TAX,BPDD.SALEPRICE,BPDD.PROFIT,BPDD.REMARK, BP.CUSTOMERNAME,BP.CREATTIME,BPOI.Place1,BPOI.Place2,BPOI.Place3,BPOI.Place4, BPOI.Place5, BPOI.Carrier1 ,BPOI.Carrier2,BPOI.Carrier3,BPOI.Carrier4,BPOI.Class1,BPOI.Class2,BPOI.Class3,BPOI.Class4, BPOI.Flight1, BPOI.Flight2,BPOI.Flight3,BPOI.Flight4, BPOI.Date1,ISNULL(BPOI.TourCode,'') AS TOURCODE , BP.GRUP,BPDD.INPRICE,BPDD.OUTPRICE ,CASE SUBSTRING(BPOI.DATE1,3,3) WHEN 'JAN' THEN '190001'+substring(date1,1,2) WHEN 'FEB' THEN '190002'+substring(date1,1,2) WHEN 'MAR' THEN '190003'+substring(date1,1,2) WHEN 'APR' THEN '190004'+substring(date1,1,2) WHEN 'MAY' THEN '190005'+substring(date1,1,2) WHEN 'JUN' THEN '190006'+substring(date1,1,2) WHEN 'JUL' THEN '190007'+substring(date1,1,2) WHEN 'AUG' THEN '190008'+substring(date1,1,2) WHEN 'SEP' THEN '190009'+substring(date1,1,2) WHEN 'OCT' THEN '190010'+substring(date1,1,2) WHEN 'NOV' THEN '190011'+substring(date1,1,2) WHEN 'DEC' THEN '190012'+substring(date1,1,2) END AS DATEORDER from BILLPAYPRODUCTDTL BPDD INNER JOIN BILLPAYPRODUCT BP ON BP.BILLNO=BPDD.BILLNO INNER JOIN BillPayOUTITicket BPOI ON BPOI.BOOKPID=BPDD.BOOKPID AND LEN(BPOI.DATE1)=5 这样就变成字符串比较了,比较笨不过办法可行!
ID INT , FDATE NVARCHAR(20))
INSERT INTO MT SELECT 1, '01JUN'
UNION SELECT 2, '02FEB'
UNION SELECT 3, '21JUN'
UNION SELECT 4, '03JAN'
UNION SELECT 5, '25NOV'
UNION SELECT 6, '30DEC'
declare @s nvarchar(20)
set @S='01JAN'
declare @e nvarchar(20)
set @e='01JUN'SELECT ID ,FDATE FROM MT
where cONVERT(datetime, stuff(FDATE,3,0,' ')+' 1900') between
cONVERT(datetime, stuff(@S,3,0,' ')+' 1900') and cONVERT(datetime, stuff(@e,3,0,' ')+' 1900')ID FDATE
----------- --------------------
1 01JUN
2 02FEB
4 03JAN(3 row(s) affected)
SQL Server 可以识别以下列格式括在单引号 (') 中的日期和时间: 字母日期格式(例如,'April 15, 1998')
数字日期格式(例如,'4/15/1998'、'April 15,1998')
未分隔的字符串格式(例如'19981207'、'December 12, 1998')
但是我用 'April 15,1998'也会出错..
select Convert(datetime,'April 15,1998')
------------------------------------------------------
1998-04-15 00:00:00.000(1 row(s) affected)
自己后来解决了,你的方法有漏洞,就是碰到闰年的情况,两月分可能天数有变化,转换的时候就错误。
CREATE VIEW dbo.BP_BPDD_BPI
AS
select BPDD.PRECODE,BPDD.PRODUCTID,BPDD.PRODUCTRELATION1,BPDD.PRODUCTRELATION2,BPDD.EXCHANGEKIND,(BPDD.RATE*BPDD.QVALUE) AS QVALUE,BPDD.TAX,BPDD.SALEPRICE,BPDD.PROFIT,BPDD.REMARK,
BP.CUSTOMERNAME,BP.CREATTIME,BPI.Place1,BPI.Place2,BPI.Place3,BPI.Place4, BPI.Place5, BPI.Carrier1
,BPI.Carrier2,BPI.Carrier3,BPI.Carrier4,BPI.Class1,BPI.Class2,BPI.Class3,BPI.Class4, BPI.Flight1, BPI.Flight2,BPI.Flight3,BPI.Flight4, BPI.Date1,ISNULL(BPI.TourCode,'') AS TOURCODE ,BP.GRUP,BPDD.INPRICE,BPDD.OUTPRICE
, CASE SUBSTRING(BPI.DATE1,3,3)
WHEN 'JAN' THEN '190001'+substring(date1,1,2)
WHEN 'FEB' THEN '190002'+substring(date1,1,2)
WHEN 'MAR' THEN '190003'+substring(date1,1,2)
WHEN 'APR' THEN '190004'+substring(date1,1,2)
WHEN 'MAY' THEN '190005'+substring(date1,1,2)
WHEN 'JUN' THEN '190006'+substring(date1,1,2)
WHEN 'JUL' THEN '190007'+substring(date1,1,2)
WHEN 'AUG' THEN '190008'+substring(date1,1,2)
WHEN 'SEP' THEN '190009'+substring(date1,1,2)
WHEN 'OCT' THEN '190010'+substring(date1,1,2)
WHEN 'NOV' THEN '190011'+substring(date1,1,2)
WHEN 'DEC' THEN '190012'+substring(date1,1,2)
END AS DATEORDER from BILLPAYPRODUCTDTL BPDD INNER JOIN BILLPAYPRODUCT BP ON
BP.BILLNO=BPDD.BILLNO INNER JOIN BillPayITicket BPI
ON BPI.BOOKPID=BPDD.BOOKPID AND LEN(BPI.DATE1)=5
UNION ALL
select BPDD.PRECODE,BPDD.PRODUCTID,BPDD.PRODUCTRELATION1,BPDD.PRODUCTRELATION2,BPDD.EXCHANGEKIND,(BPDD.RATE*BPDD.QVALUE) AS QVALUE,BPDD.TAX,BPDD.SALEPRICE,BPDD.PROFIT,BPDD.REMARK,
BP.CUSTOMERNAME,BP.CREATTIME,BPOI.Place1,BPOI.Place2,BPOI.Place3,BPOI.Place4, BPOI.Place5, BPOI.Carrier1
,BPOI.Carrier2,BPOI.Carrier3,BPOI.Carrier4,BPOI.Class1,BPOI.Class2,BPOI.Class3,BPOI.Class4, BPOI.Flight1, BPOI.Flight2,BPOI.Flight3,BPOI.Flight4, BPOI.Date1,ISNULL(BPOI.TourCode,'') AS TOURCODE , BP.GRUP,BPDD.INPRICE,BPDD.OUTPRICE
,CASE SUBSTRING(BPOI.DATE1,3,3)
WHEN 'JAN' THEN '190001'+substring(date1,1,2)
WHEN 'FEB' THEN '190002'+substring(date1,1,2)
WHEN 'MAR' THEN '190003'+substring(date1,1,2)
WHEN 'APR' THEN '190004'+substring(date1,1,2)
WHEN 'MAY' THEN '190005'+substring(date1,1,2)
WHEN 'JUN' THEN '190006'+substring(date1,1,2)
WHEN 'JUL' THEN '190007'+substring(date1,1,2)
WHEN 'AUG' THEN '190008'+substring(date1,1,2)
WHEN 'SEP' THEN '190009'+substring(date1,1,2)
WHEN 'OCT' THEN '190010'+substring(date1,1,2)
WHEN 'NOV' THEN '190011'+substring(date1,1,2)
WHEN 'DEC' THEN '190012'+substring(date1,1,2)
END AS DATEORDER from BILLPAYPRODUCTDTL BPDD INNER JOIN BILLPAYPRODUCT BP ON
BP.BILLNO=BPDD.BILLNO INNER JOIN BillPayOUTITicket BPOI
ON BPOI.BOOKPID=BPDD.BOOKPID AND LEN(BPOI.DATE1)=5
这样就变成字符串比较了,比较笨不过办法可行!