CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Sami
Sami

Reputation: 3976

Filter by month name

I want to retrieve data on the basis of months. I want to get worksheet data between two months following is my query but it doesn't work fine.

SELECT  (CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) +  '-' +     CONVERT(VARCHAR(15), datepart(yyyy, w.ExpectedStartDate))) as MonthName

    FROM Worksheet w LEFT OUTER JOIN StatusType st ON st.StatusTypeId = w.StatusTypeId
    WHERE w.ProjectId = 20
    AND CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) between ('Jan')     AND ('Apr')

When i convert it to this:

SELECT  (CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) +  '-' +     CONVERT(VARCHAR(15), datepart(yyyy, w.ExpectedStartDate))) as MonthName

        FROM Worksheet w LEFT OUTER JOIN StatusType st ON st.StatusTypeId = w.StatusTypeId
        WHERE w.ProjectId = 20
        AND CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)) between ('Jan')     AND ('Mar')

I have just changed ToMonth = Mar rather than Apr. then it shows me record which is correct because there is data for march. for April why it doesn't show any thing?

Please give your suggestion.

Thanks.

Upvotes: 0

Views: 1405

Answers (5)

marc_s
marc_s

Reputation: 754268

If you need to do this query frequently, you could also investigate persisted computed columns - e.g. something like:

ALTER TABLE dbo.Worksheet 
  ADD YearMonth AS 
        RIGHT('0000' + CAST(YEAR(ExpectedStartDate) AS VARCHAR(4)), 4) + 
        RIGHT('00' + CAST(MONTH(ExpectedStartDate) AS VARCHAR(2)), 2) PERSISTED

This will add a new column YearMonth to your table that is always up to date, always accurate, and it's persisted, e.g. stored on disk so it's fast, you can even put an index on that column!

That column will contains values such as

201103
201104

and so forth - the year (four digits) and month (two digits, leading zero) of your ExpectedStartDate

So to select all projects for January 2011, you can define:

 SELECT (list of columns)
 FROM dbo.Worksheet
 WHERE YearMonth = '201101'

and get exactly those rows very quickly - especially if you index this column.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

For accuracy use this code

w.ExpectedStartDate>='20110101' and ExpectedStartDate<'20110105'

Upvotes: 0

Curtis
Curtis

Reputation: 103348

AND MONTH(w.ExpectedStartDate)>=1 AND MONTH(w.ExpectedStartDate)<=4

Upvotes: 0

Bassetassen
Bassetassen

Reputation: 21784

You can use Month(w.ExpectedStartDate) between 1 AND 4

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

I imagine it doesn't like the fact you've used BETWEEN with strings; the string 'Apr' comes before the string 'Jan' so it doesn't work, but the string 'Mar' comes after the string 'Jan'.

Upvotes: 3

Related Questions