問題描述

我看了很多回答我的問題，但沒有一個真正回答我的問題.

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 ...

這篇關于將月份名稱轉換為日期/月份編號(問題和答案的組合)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！