How to split the date format into month with year?

Question

Post reply

How to split the date format into month with year?

raghuldrag

Hall of Fame

Points: 3160
More actions
December 31, 2013 at 2:26 am

#284078

create table estr
(
custo int,
product varchar(20),
val int,
billed_date datetime
)
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-11','2000')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-21','500')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-05','100')
insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-11','2000')
insert into estr(custo,product,billed_date,value) values('10','fruits','2013-05-11','500')
insert into estr(custo,product,billed_date,value) values('30','fruits','2013-04-11','2000')
insert into estr(custo,product,billed_date,value) values('30','fruits','2013-05-11','1000')
from here i wanna extract the month with year for counting purpose how ll do ?
guide me?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Share your expected output as well for better understanding.

raghuldrag Hall of Fame Points: 3160 More actions · Answer 2

custoproductvalno.of.mon

10Friut 1300 1

10Milk 2900 2

30Milk 6000 2

i wanna calculate no.of.months along with year ?

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

raghuldrag (12/31/2013)
custoproductvalno.of.mon
10Friut 1300 1
10Milk 2900 2
30Milk 6000 2
i wanna calculate no.of.months along with year ?

what representation for 'no.of.mon' column ? need more details

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

raghuldrag Hall of Fame Points: 3160 More actions · Answer 5

i need the output like

for example 2013-04-01 is in table my expectation output is 04-2013 (extract month with year)......

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 6

raghuldrag (12/31/2013)
i need the output like
for example 2013-04-01 is in table my expectation output is 04-2013 (extract month with year)......

Hi.

This seems to be a relatively simple single SELECT statement.

Please use DATEPART to convert billed_date into a year with yyyy, and a second datepart to get the month with mm.

Then group your query the way you need to summarize the data, using SUM to add up values.

Thanks

John.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Assuming this is still true: billed_date datetime

right('0' + cast(month(billed_date) as varchar(2)),2) + '-' + cast(year(billed_date) as varchar(4))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Now, I have a suggestion. You should get to know Books Online. In SSMS press Shift-F1. There is a lot of good information in there and you don't have to rely on waiting for volunteers to answer all your questions. If something doesn't make sense there, come ask questions here.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

Lynn Pettis (12/31/2013)
Assuming this is still true: billed_date datetime
right('0' + cast(month(billed_date) as varchar(2)),2) + '-' + cast(year(billed_date) as varchar(4))

... or...

SELECT RIGHT(CONVERT(CHAR(10),GETDATE(),105),7)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jeff Moden (12/31/2013)
Lynn Pettis (12/31/2013)
Assuming this is still true: billed_date datetime
right('0' + cast(month(billed_date) as varchar(2)),2) + '-' + cast(year(billed_date) as varchar(4))
... or...
SELECT RIGHT(CONVERT(CHAR(10),GETDATE(),105),7)

To the OP: And you can learn about COVERT and the different format codes it accepts by reading Books Online as well.

Remember, Books Online can be your friend.