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

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
Posted

Use functions:
MONTH[^]
YEAR[^]
DATEPART[^]
   
Comments
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.
Hi,

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

more on DatePart refer DATEPART-MSDN[^]

hope it helps.
   
Comments
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
SQL
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!
:)
   
v2
Comments
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
   
welcome!
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