How to Strip Out Date from a Date Field

Carlton Patterson 741 Reputation points
2022-10-10T09:33:12.81+00:00

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.

248910-datefieldmod.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 41,331 Reputation points
    2022-10-10T09:41:14.637+00:00

    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  
    

3 additional answers

Sort by: Most helpful
  1. Sreeju Nair 11,776 Reputation points
    2022-10-10T09:39:31.127+00:00

    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  
    

    ![248857-image.png]1

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

    248971-image.png

    0 comments No comments

  2. NikoXu-msft 1,911 Reputation points
    2022-10-10T09:44:05.07+00:00

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

    0 comments No comments

  3. Tom Phillips 17,716 Reputation points
    2022-10-10T12:26:47.247+00:00

    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/

    0 comments No comments