CODEWITHSUNDEEP
sqlsql-server-2005
Akshay
Akshay

Reputation: 151

How to treat a string as a month in SQL Server

In my database I have a table in which one of the columns is storing joining_months in string format like January, February, March....(and so on).

Now I want to run a SQL query on this table to fetch those rows only in which joining month is before or after a user specified month.

Something like:

select * from table1 where joining_month >or< 'March'

How can I compare the months stored in string format like this?

Please help !

Upvotes: 0

Views: 87

Answers (2)

Hari Menon
Hari Menon

Reputation: 35405

I found this rather out-of-the-box way of doing this on this page:

Another way to do this would be: SELECT CHARINDEX(monthname,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber

You can modify it by first extracting the first 3 characters of the month from your table and comparing that to the hard-coded string 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC'. That should do it.

So your query can be something like this:

   select CHARINDEX(substing(joining_month,1,3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3
            AS monthnumber, * 
            from table1 
            where monthnumber >or< 3

Upvotes: 3

casperOne
casperOne

Reputation: 74530

You should have another column on the table which has the numeric value of the month associated with the name, e.g. 1 for "January", 2 for "February", etc.

Then, you can simply filter on the name of the string while doing the comparison on the numeric value.

Just be cautious, a direct comparison like this only works if the months are in dates that are for the same year. If they are in different years, then comparing the months is pointless unless you take the year into account.

Upvotes: 3

Related Questions