Click here to Skip to main content
15,890,579 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a date field name as "InvoiceDate" in my table that have a date information like this "20140231", I want to get the month out of that date field so that I can group my record per quarter see the attached query that I tried, currently the date field is hard coded as you see on the query.
Can you please help how can I get the month on this record
"20140231", 


What I have tried:

This what I tried
SELECT TOP 1000 [Advise no]
      ,[Amount]
      ,[Date]
      ,[Bas Fund Description]
	  ,[Invoice Date]
         (MONTH, DATEDIFF(MONTH, 0, [Invoice Date]), 0) AS months
	   , CASE WHEN [Invoice Date] BETWEEN 20170401 AND 20170631
             THEN 'Q1' 
           WHEN [Invoice Date] BETWEEN 20170701 AND 20170931
             THEN 'Q2' 
           WHEN [Invoice Date] BETWEEN 20171001 AND 20171231
             THEN 'Q3'
           WHEN [Invoice Date] BETWEEN 20180101 AND 20180331 
              THEN 'Q4' 
           WHEN [Invoice Date] Is Null 
              THEN 'Null' 

        END AS QuarterPeriod 
      ,[Valuation Date]
      ,[qgs_geometry]
      ,[qgs_fid]
      ,[ProvinceName]
  FROM [DOD].[dbo].[View_DPW_PACE]
Posted
Updated 16-Apr-18 21:18pm
Comments
Richard MacCutchan 17-Apr-18 3:16am    
You should use proper Date/DateTime types, which allow direct access to individual items of the date.

1 solution

SQL
SELECT MONTH('2014-02-31')
See: MONTH (Transact-SQL) | Microsoft Docs[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900