将月份名称转换为日期/月份编号(问题和答案的组合)
我看了很多回答我的问题，但没有一个真正回答我的问题.
I had a look at numerous answer to my question, but none really answered my question.
我有数据:
cBatchDesc idBatches
Journal Batch - March 2017 88
Journal Batch - April 2017 2
Journal Batch - May 2017 3
Journal Batch - June 2017 4
Journal Batch - July 2017 5
Journal Batch - August 2017 6
Journal Batch - September 2017 7
Journal Batch - October 2017 8
Journal Batch - November 2017 9
Journal Batch - December 2017 10
Journal Batch - January 2018 11
Journal Batch - February 2018 12
Journal Batch - March 2017 89
Journal Batch - April 2017 13
Journal Batch - May 2017 14
Journal Batch - June 2017 15
Journal Batch - July 2017 16
Journal Batch - August 2017 17
Journal Batch - September 2017 18
Journal Batch - October 2017 19
Journal Batch - November 2017 20
Journal Batch - December 2017 21
Journal Batch - January 2018 22
Journal Batch - February 2018 23
Journal Batch - March 2017 90
Journal Batch - April 2017 27
Journal Batch - May 2017 28
Journal Batch - June 2017 29
Journal Batch - July 2017 30
Journal Batch - August 2017 31
Journal Batch - September 2017 32
Journal Batch - October 2017 33
Journal Batch - November 2017 34
Journal Batch - December 2017 35
Journal Batch - January 2018 36
Journal Batch - February 2018 37
我需要将 cBatchDesc 末尾的日期名称转换为实际日期.
I needed to convert the date name at the end of cBatchDesc to the Actual Date.
推荐答案
首先，如果您发布的数据是易于使用的"，例如以下内容，人们会更愿意提供帮助...
First of all, people would be a whole lot more willing to help if the data you post is "Readily Consumable", like the following...
DROP TABLE IF EXISTS #TestTable;
GO
SELECT v.cBatchDesc, v.idBatches
INTO #TestTable
FROM (VALUES
('Journal Batch - March 2017' ,88)
,('Journal Batch - April 2017' ,2 )
,('Journal Batch - May 2017' ,3 )
,('Journal Batch - June 2017' ,4 )
,('Journal Batch - July 2017' ,5 )
,('Journal Batch - August 2017' ,6 )
,('Journal Batch - September 2017',7 )
,('Journal Batch - October 2017' ,8 )
,('Journal Batch - November 2017' ,9 )
,('Journal Batch - December 2017' ,10)
,('Journal Batch - January 2018' ,11)
,('Journal Batch - February 2018' ,12)
,('Journal Batch - March 2017' ,89)
,('Journal Batch - April 2017' ,13)
,('Journal Batch - May 2017' ,14)
,('Journal Batch - June 2017' ,15)
,('Journal Batch - July 2017' ,16)
,('Journal Batch - August 2017' ,17)
,('Journal Batch - September 2017',18)
,('Journal Batch - October 2017' ,19)
,('Journal Batch - November 2017' ,20)
,('Journal Batch - December 2017' ,21)
,('Journal Batch - January 2018' ,22)
,('Journal Batch - February 2018' ,23)
,('Journal Batch - March 2017' ,90)
,('Journal Batch - April 2017' ,27)
,('Journal Batch - May 2017' ,28)
,('Journal Batch - June 2017' ,29)
,('Journal Batch - July 2017' ,30)
,('Journal Batch - August 2017' ,31)
,('Journal Batch - September 2017',32)
,('Journal Batch - October 2017' ,33)
,('Journal Batch - November 2017' ,34)
,('Journal Batch - December 2017' ,35)
,('Journal Batch - January 2018' ,36)
,('Journal Batch - February 2018' ,37)
)v(cBatchDesc,idBatches)
;
我很高兴你自己解决了这个问题，但你可以通过计算-"的位置来让事情变得更灵活一些.这样您就可以处理其他描述(如果发生).
I'm glad you solved it on your own but you can make things a little more flexible by calculating the position of the "-" so you can handle other descriptions, should they occur.
此外，您可以干燥"(D不要R重复Y我们自己)您的代码(以便更容易阅读/更改)只需计算一次日期对于每一行使用这样的 CROSS APPLY(请注意，您没有提供 cBatchNo 列，因此 ORDER BY 不同，我没有显示 SUBSTRING)...
Also, you can "DRY" (Don't Repeat Yourself) your code out (to make it easier to read/change) by calculating the date just once for each row using a CROSS APPLY like this (note that you didn't provide a cBatchNo column so the ORDER BY is different and I didn't display the SUBSTRING)...
SELECT cBatchDesc
,MonthNumber = DATEPART(mm,ca.Date)
,MonthStart = ca.Date
,MonthEnd = EOMONTH(ca.Date)
,idBatches
FROM #TestTable
CROSS APPLY (SELECT CONVERT(DATE,SUBSTRING(cBatchDesc,CHARINDEX('-',cBatchDesc)+1,100)))ca(Date)
ORDER BY ca.Date, idBatches
;
对于您提供的数据，提供以下(截断的)结果......
For the data you provided, that provides the following (truncated) results ...
相关文章