CODEWITHSUNDEEP
sql-servert-sqldatetime-format
Srikanth
Srikanth

Reputation: 39

Format function is not working at where clause to filter the format date?

Getting the months list of current year and Previous year

  SELECT FORMAT(DATEADD(month,number,CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS varchar(4)) + '-01-01'),'MMM') + '-' +
                   SUBSTRING(CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + IIF(number<12,-1,0) AS VARCHAR(4)),3,2) as Months into #temptable
            FROM master..spt_values
            WHERE type = 'P'
            AND number < 24

Expected Output:-1

From Current Month-                 Mar-19
                                    Feb-19
End of CurrentYear Month           -Jan-19

ExpectedOutPut:2 

Jan-18
to 
Dec -18

I'm trying this query 

select *from #temptable where Months>=FORMAT(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0),'MMM-yy') and Months<=FORMAT(DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0),'MMM-yy') 
select *from #temptable where Months>=FORMAT(DATEADD(yy, DATEDIFF(yy, 0, getdate())-1, 0),'MMM-yy') and Months<= FORMAT(DATEADD(yy, DATEDIFF(yy, 0, getdate()), -1),'MMM-yy')

Upvotes: 0

Views: 184

Answers (1)

Richard Hansell
Richard Hansell

Reputation: 5403

Seeing as you are essentially storing your month/ years as text, to actually compare them to dates is painful at best. You would be much better storing either real dates, or years/ months as two separate values, maybe with a text version as well for display purposes?

Anyway, based on your poor data model you could do this to get what you need:

SELECT * FROM #temptable WHERE YEAR(CONVERT(DATE, '01-' + Months)) = YEAR(GETDATE()) - 1;
SELECT * FROM #temptable WHERE YEAR(CONVERT(DATE, '01-' + Months)) = YEAR(GETDATE()) AND MONTH(CONVERT(DATE, '01-' + Months)) <= MONTH(GETDATE());

But note that this now introduces a dependency on your regional format, as this wouldn't work in America (for example) as day comes after month. You would be far better using ANSI date format of YYYYMMDD (or storing the dates as year/ months, or hey(!) even as dates).

Upvotes: 3

Related Questions