CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Vishal
Vishal

Reputation: 12369

How to get the month from a string in sql?

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?

Upvotes: 9

Views: 16015

Answers (7)

harvest316
harvest316

Reputation: 1411

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.

Upvotes: 0

Nipuna
Nipuna

Reputation: 7006

    datepart(month,_datetime)

where _datetime is the column. you can also use 

    datepart(month,getdate())

to get the current month

Upvotes: 0

vol7ron
vol7ron

Reputation: 42109

SELECT month(column);

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

Upvotes: 0

Rushyo
Rushyo

Reputation: 7604

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.

Upvotes: 0

Beth
Beth

Reputation: 9607

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.

Upvotes: 1

Jeff Ogata
Jeff Ogata

Reputation: 57783

also this:

SELECT MONTH(CAST(date_col AS datetime))

Upvotes: 3

Denis Valeev
Denis Valeev

Reputation: 6015

Try this:

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

Upvotes: 12

Related Questions