Add week number column in query results from existing date column

February 16, 2023

I am just learning SQL, using SQL Server Management Studio.

The current query is shown below and I am struggling with trying to add a column to my results query, where it converts the PRODUCTION_DATE into fiscal week.

SELECT        
    ORDER_QTY, SKU, INVOICE_NUMBER, CUSTOMER_NUMBER, ROUTE, 
    ALLOCATED_QTY, SHORTED_QTY, PRODUCTION_DATE
FROM          
    [DATEBASE_NAME].[XYZ].[ORDERS]
WHERE 
    [PRODUCTION_DATE] >= DATEADD(day, -300, GETDATE())  
    AND [PRODUCTION_DATE] <= GETDATE()

I believe DATEPART is part of the function, I just don’t know how to execute. End goal would be a column in the results that just returns the fiscal week along with all other data. The little query above returns my data proper now, I am just trying to get that column in.

Thanks for looking.

>Solution :

As you have already mentioned about DATEPART;

(And you were almost there !!)

Here is the modified query

SELECT        
    ORDER_QTY, SKU, INVOICE_NUMBER, CUSTOMER_NUMBER, ROUTE, 
    ALLOCATED_QTY, SHORTED_QTY, PRODUCTION_DATE,
    DATEPART(wk, PRODUCTION_DATE) AS FISCAL_WEEK
FROM          
    [DATEBASE_NAME].[XYZ].[ORDERS]
WHERE 
    [PRODUCTION_DATE] >= DATEADD(day, -300, GETDATE())  
    AND [PRODUCTION_DATE] <= GETDATE();