Query for getting current and previous two months records from varchar datatype

Question

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 18-Feb-16 1:20am

kirthiga S

Updated 18-Feb-16 19:22pm

Add a Solution

3 solutions

Solution 1

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

Posted 18-Feb-16 1:32am

Herman<T>.Instance

Solution 2

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.

Posted 18-Feb-16 1:33am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kirthiga S · Accepted Answer · 2016-02-18T19:22:00

Solution 3

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)

Posted 18-Feb-16 19:22pm

kirthiga S