Split date into month & year wise

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 1-Apr-13 22:57pm

Naveen.Sanagasetti

Add a Solution

3 solutions

Solution 1

Use functions:
MONTH[^]
YEAR[^]
DATEPART[^]

Posted 1-Apr-13 23:00pm

Maciej Los

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.

Solution 2

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.

Posted 1-Apr-13 23:03pm

Karthik Harve

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Accepted Answer · 2013-04-01T23:16:00

Solution 3

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!
:)

Posted 1-Apr-13 23:16pm

Aarti Meswania

Updated 1-Apr-13 23:22pm

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