Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I have a Date column like this in my sql server.

ex: jan-2013, feb-2013....

now i want to split that into month & year wise

month ----jan-1, year---2013

Use functions:
Naveen.Sanagasetti 2-Apr-13 5:03am
If my date is '2013-04-02 14:36:10.300' this format then i know how to fetch that month && year details month(date),year(date).

But my date is varchar type and my date is look like this 'jan-2013'....
Maciej Los 2-Apr-13 5:31am
Bad table design!
Please, redesign your table, than use: Date and Time functions[^]
Next time, please, be more specific and provide details abuot your table structure.
Naveen.Sanagasetti 3-Apr-13 5:15am
That is not a table , in my SP one of the column and that SP i'm using to Generate the Report purpose..

i already given the details in my question itself
ex: jan-2013, feb-2013....

month ----jan-1, year---2013 .

Once pl study my question again
Maciej Los 3-Apr-13 5:34am
I can't agree, because you have wrote: I have a Date column like this in my sql server. This is information, that table contains field/column with datetime data type. Only in comment you have explained that date is stored in varchar field. Now, you are writing that is not table, but SP...

Show me the structure of table and i'll help you to solve your problem... Use "Improve question" widget.

try below query.
SELECT DATEPART(MONTH, DateColumn) 'Month', DATEPART(YEAR, DateColumn) 'Year' FROM YourTable

more on DatePart refer DATEPART-MSDN[^]

hope it helps.
Naveen.Sanagasetti 2-Apr-13 5:06am
SELECT DATEPART(MONTH, 'jan-2013') 'Month', DATEPART(YEAR, 'jan-2013') 'Year'

i'm getting this error message "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
Karthik Harve 2-Apr-13 5:12am
That works, only if the column is of type datetime.
Naveen.Sanagasetti 2-Apr-13 5:14am
Ok fine but my query is look like this
SELECT DATEPART(MONTH, 'jan-2013') 'Month', DATEPART(YEAR, 'jan-2013') 'Year'

how to fetch month && year details
try this
declare @date varchar(max)
set @date='jan-2013'
select right(@date,4) as Year, 
	substring(@date,0,len(@date)-4) as MonthName,
	datepart(month,'1900-' + substring(@date,0,len(@date)-4) + '01' ) as MonthNumber

Happy Coding!
Naveen.Sanagasetti 2-Apr-13 5:28am
thank you this works fine for me,
but here month means it's showing monthname but i want month number without using case is it possible to display month number
Aarti Meswania 2-Apr-13 5:29am
Glad to help you! :)
check again,
it will give you Month number also
Naveen.Sanagasetti 2-Apr-13 5:30am
hi i tried like this this works fine for me

select month(cast('01-'+'feb-2012' as datetime)),year(cast('01-'+'feb-2012' as datetime))
Aarti Meswania 2-Apr-13 5:31am
ok good

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900