How to Strip Out Date from a Date Field

Question

Hi Community,

Can someone help with a query that will strip out the year from a date field and place it in another column, as well as the quarter.

For example I would like the following view.

Sample Data

CREATE TABLE #tmpTable (  
    financials_1_accountsDate datetime2)  
  
INSERT #tmpTable VALUES  
(CONVERT(DATETIME2, '2020-12-31 00:00:00.0000000', 121)),  
(CONVERT(DATETIME2, '2020-03-31 00:00:00.0000000', 121)),  
(CONVERT(DATETIME2, '2020-12-31 00:00:00.0000000', 121))  
  
SELECT * FROM #tmpTable  
  
  

Thanks

Answer 1

You can simply calculate it on the fly with

 SELECT *, YEAR(financials_1_accountsDate) AS Year,   
           'Q' + CONVERT(varchar(1), DATEPART(qq, financials_1_accountsDate)) AS Quarter  
 FROM #tmpTable  

Answer 2

You may use the DatePart function for this. see the sample below.

 SELECT DatePart(year, financials_1_accountsDate)  FROM #tmpTable  
SELECT DatePart(year, financials_1_accountsDate) Year, DatePart(quarter, financials_1_accountsDate) Quarter  FROM #tmpTable  

]1

Refer: https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16

Answer 3

Hi @Carlton Patterson ,

Try this code:

 select   
 datepart(year,financials_1_accountsDate)as year ,  
  'Q'+cast(datepart(quarter,financials_1_accountsDate)as varchar(10))as quarter  
from #tmpTable   

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

You would be better off using a calendar table as a lookup table and never think about this again, rather than calculate this on the fly.

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/