How to treat a string as a month in SQL Server

Question

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 !

Answer 1

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.

Answer 2

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