divide or seperate date's month and year in to two column

Question

4.00/5 (1 vote)

See more:

i want to divide months and years in to two seperate colums.

TranDate
31/01/2013
31/02/2013
31/03/2013

i want to creat sql query which will seperate month and year from one column and put months into one column and year in to other column.
so in total there will be three column.

eg:

TranDate month year
31/12/2013 12 2012
31/01/2013 01 2013
31/02/2013 02 2013

Posted 21-Feb-13 23:57pm

ByakuyaKuchiki

Add a Solution

3 solutions

Solution 1

Hi Try This

SQL

SELECT TranDate, MONTH(TranDate) 'TranMonth', YEAR(TranDate) 'TranYear' 
FROM table_name

Check all System Date Functions

Date and Time Functions (Transact-SQL)[^]

Regards,
GVPrabu

Posted 22-Feb-13 0:03am

gvprabu

Updated 22-Feb-13 0:13am

v2

Solution 3

1. Change your Datatype by following statement

ALTER TABLE Table_Name ALTER COLUMN TranDate DateTime

2. USE SubString(Expression, Start, End)

SELECT TranDate, substring(TranDate,4,5) AS Month, substring(TranDate,7,10) AS Year
FROM Table_Name

Posted 22-Feb-13 1:30am

MalwareTrojan

Updated 22-Feb-13 1:55am

v2

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)

Chris Reynolds (UK) · Accepted Answer · 2013-02-22T00:04:00

Solution 2

SQL

SELECT TranDate, MONTH(TranDate) as 'month', YEAR(TranDate) as 'year' FROM TableName

OP has come back with an error that indicates his TranDate column is not a datetime so he can either convert it to a datetime

SQL

SELECT TranDate, MONTH(convert(datetime, TranDate, 103)) as 'month', YEAR(convert(datetime, TranDate, 103)) as 'year' FROM TableName

or use SUBSTRING to get the parts he wants

SELECT TranDate, SUBSTRING(TranDate, 4,2), SUBSTRING(TranDate, 7,4)

Each have their pros and cons, depending on how sure you are that the data on thr database is correct

Posted 22-Feb-13 0:04am

Chris Reynolds (UK)

Updated 22-Feb-13 1:32am

v3

Comments

ByakuyaKuchiki 22-Feb-13 7:23am

i am getting "Conversion failed when converting date and/or time from character string" error.
i want to convert my date field in to date and time, its currently varchar(50) using sql Query.

Chris Reynolds (UK) 22-Feb-13 7:46am

I have updated the solution for you.

ByakuyaKuchiki 25-Feb-13 1:42am

Thank you its working.