Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to split the date format into month with year? Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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?
Post #1526692
Posted Tuesday, December 31, 2013 2:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 752, Visits: 1,323
Share your expected output as well for better understanding.
Post #1526694
Posted Tuesday, December 31, 2013 2:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
custo product val no.of.mon
10 Friut 1300 1
10 Milk 2900 2
30 Milk 6000 2


i wanna calculate no.of.months along with year ?
Post #1526695
Posted Tuesday, December 31, 2013 2:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 752, Visits: 1,323
raghuldrag (12/31/2013)
custo product val no.of.mon
10 Friut 1300 1
10 Milk 2900 2
30 Milk 6000 2


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



what representation for 'no.of.mon' column ? need more details
Post #1526697
Posted Tuesday, December 31, 2013 2:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1526700
Posted Tuesday, December 31, 2013 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427

i need the output like

for example 2013-04-01 is in table my expectation output is 04-2013 (extract month with year)......
Post #1526703
Posted Tuesday, December 31, 2013 11:36 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, September 28, 2014 2:10 PM
Points: 692, Visits: 325
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.
Post #1526822
Posted Tuesday, December 31, 2013 6:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 20,705, Visits: 32,354
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1526869
Posted Tuesday, December 31, 2013 6:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 20,705, Visits: 32,354
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1526870
Posted Tuesday, December 31, 2013 10:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526880
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse