CODEWITHSUNDEEP
sql-servert-sql
user1621796
user1621796

Reputation: 3

Data from last 13 months

I've a table in SQL Server 2008 database which has a column 'Month', nvarchar datatype, where the data is stored in the format 'Jan 2013', 'Feb 2013', 'Mar 2013' and so on till 'Mar 2014'.

Now I want the data from Mar 2013 to Mar 2014 in my result but am not able to get that.

Am trying this where clause : WHERE (Month > DATEADD(Month, DATEDIFF(year, 0, GETDATE()) - 13, 0)) but it's giving me an error saying Conversion failed when converting date and/or time from character string.

I've also tried renaming the data as 'March 2014' and subsuquently for other entries as well, but it's not helping either.

Dear SQL experts, please help!

Upvotes: 0

Views: 1199

Answers (2)

Hedinn
Hedinn

Reputation: 864

you could do something like this

SELECT *
FROM table 
where (LEFT(month,3) not in ('jan','feb') and right(month,4)='2013')
OR (LEFT(month,3) in ('jan','feb') and right(month,4)='2014')

The resultset will include March 2013 but not March 2014 if you want to include march 2014 change the last part of the where clause to (LEFT(month,3) in ('jan','feb','mar') and right(month,4)='2014') But I recommend that you store your dates as datetime

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Yucky format for date. My advice is to convert it to an actual date. Happily, SQL Server recognizes the following format:

select convert(date, '15 MAR 2014')

So, you can do:

where convert(date, '15 ' + [Month]) > DATEADD(Month, DATEDIFF(year, 0, GETDATE()) - 13, 0)

I arbitrarily set the date to the middle of the month. You might want to use '01' rather than '15'.

Upvotes: 0

Related Questions