Click here to Skip to main content
15,945,939 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with two columns
[MISMONTH] --> current month lastdate in varchar datatype
[SRIMONTH] --> Year and Month records in varchar datatype

MISMONTH SRIMONTH
20160229 201511
20160229 201601
20160229 201602
20160229 201512


What I have tried:

I have a query to get a current month data

select * from MyTable where SRIMONTH = Left(MISMONTH,6)

I need a query for getting current and previous two months records from [SRIMONTH] by referring [MISMONTH]
Posted
Updated 18-Feb-16 19:22pm

Something like this:
SQL
SELECT CAST(MISMONTH As DateTime) as MisMonth, (YEAR(DateADD(mm, -2, CAST(MISMONTH As DateTime)) * 100) + (MONTH(DateADD(mm, -2, CAST(MISMONTH As DateTime))) as SRIMonth
From MyTable
 
Share this answer
 
Don't.
You can do it - but it's messy, because you have to convert both the date strings into DATE or DATETIME values for each query, and that's both nasty and very inefficient.
Instead, change your DB to store date based values in DATE or DATETIME fields - it's generally more efficient in terms of space, and it saves a whole load of hassle when you want to do anything with it, like comparisons.
You can't compare them as string values, because you can't subtract a month from a string!

Seriously, change your DB. String storage is only ever any good for string based data - everything else should be stored in the proper data types.
 
Share this answer
 
Thanks for your response.

I found out my answer

SQL
select * from MyTable where SRIMONTH >=convert(varchar(6),DATEADD(MONTH,-2,convert(datetime,MISMONTH)),112)
 
Share this answer
 

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