问题描述
我看了很多回答我的问题,但没有一个真正回答我的问题.
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 ...