How to get the month from a string in sql?

Question

I have to get the month from a column which stores string in format -

Column
----------
`Feb2007'     
'Sep2008'

So if the value was 'Feb2007' then I need to get back 2 or else if the value was 'Sep2009' then I should get back 9.

Is there inbuilt function in SQL Server 2008 to achieve something like this?

Answer 1

Try this:

select datepart(mm, cast('feb2008' as datetime))

Answer 2

If nothing else, you can create a table with your month names and numeric values.

If the server function always recognizes your month names correctly, you're good, but if any of that data was entered manually, there could be misspellings or other inconsistencies, and a table would allow you to support them.

Answer 3

also this:

SELECT MONTH(CAST(date_col AS datetime))

Answer 4

I heartily agree with Beth here - but with justifications as to why it should be done as opposed to as a last resort:

Data should be stored in its native format. You should no more store it as a string than you should an 8-bit integer. It makes just as much/little sense. SQL is a very intelligent beast, but if you screw up your fundamental data types most of its useful functionality and heuristics goes out of the window - even basic stuff like indexing and sorting. Not to mention your data becomes SQL Server explicit (ie. relies on certain SQL behaviour) as opposed to generic, reducing portability.

To put it in more formal terms: you're forcing it to become information at the data layer. This is antithesis to all known rules of data handling.

Answer 5

SELECT month(column);

This should work, it's been a while since I've used SQL Server, though.

Answer 6

    datepart(month,_datetime)

where _datetime is the column. you can also use

    datepart(month,getdate())

to get the current month

Answer 7

Might need to revisit your database design, if at all possible. Much more efficient to convert it to datetime before storing in the table. For example, finding a range of dates will take much longer than it needs to because you'd have to cast each distinct value before finding the dates in your range.