Help Converting date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Converting date

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-21 : 04:34:15

Hi All
Sorry to bother you,
I need some further help
Aim – make the following date in column “Months” displayed as the months, eg January
,February, March etc
The close date in the table ‘# Build is displayed as ” Year, Month, day” some examples are
“2010-03-26”,
“2012-03-15”
“2012-05-02”
“2012-05-08”
Query
select
DATEADD(mm,DATEDIFF(mm,0,CloseDate),0)as Months,
sum([Total_Contract_Value__c]) as Sales
FROM #build
where [Channel Indicator] = 'Field'
and [StageName] in ('Live Transactions', 'Approved by Risk')
and year(CloseDate) = '2013'
group by DATEADD(mm,DATEDIFF(mm,0,CloseDate),0)

which provides the following results
Months Sales
2013-01-01 00:00:00.000 944547.66
2013-02-01 00:00:00.000 784935.91
2013-03-01 00:00:00.000 700572.66
2013-04-01 00:00:00.000 601489.67
2013-05-01 00:00:00.000 1021441.00
2013-06-01 00:00:00.000 698085.49
2013-07-01 00:00:00.000 543487.99
2013-08-01 00:00:00.000 765430.00
2013-09-01 00:00:00.000 625714.00
2013-10-01 00:00:00.000 691163.00
2013-11-01 00:00:00.000 334358.00
2013-12-01 00:00:00.000 104794.00

Really looking for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 04:42:18

datename( month, <date column> )

KH
[spoiler]Time is always against us[/spoiler]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-11-21 : 11:04:00

Yep, what he said.

SELECT 
	DATENAME(MONTH, DATEADD(mm,DATEDIFF(mm,0,CloseDate),0))
	,SUM([Total_Contract_Value__c]) as Sales
FROM #build
WHERE [Channel Indicator] = 'Field'
	AND [StageName] in ('Live Transactions', 'Approved by Risk')
	AND YEAR(CloseDate) = '2013'
GROUP BY DATEADD(mm,DATEDIFF(mm,0,CloseDate),0)

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources