Change Month from 01 to January

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

SQL Server 2005 Forums

Transact-SQL (2005)

Change Month from 01 to January

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-29 : 14:00:29

Hi all I have been trying to change the dates on here from showing 1 to the actualy Word January. Or 02 = February. And I'm not being very successful. How would I intergrate that into this procedure? The datatype for [Start_Date] is DATETIME

[code]ALTER PROCEDURE [dbo].[YTDItDept]
AS
SELECT DATEPART(mm,Date) AS Month,
MonthITAudtiCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
MonthSecurityAdminstrationCount,
MonthInvestigationsCount,
MonthDesktopSupportCount,
MonthProfessionalDevelopmentCount,
YTDITAudtiCount,
YTDInternalApplicationsCount,
YTDMonitorCasinoIt_OpsProjCount,
YTDInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdminstrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM
(
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
sum(ITAudti) AS MonthITAudtiCount,
sum(InternalApplications) AS MonthInternalApplicationsCount,
sum(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
SUM(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
sum(SecurityAdminstration) AS MonthSecurityAdminstrationCount,
SUM(Investigations) AS MonthInvestigationsCount,
SUM(DesktopSupport) AS MonthDesktopSupportCount,
sum(ProfessionalDevelopment) AS MonthProfessionalDevelopmentCount

FROM ITDepartment
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
)t
CROSS APPLY
(
SELECT SUM(ITAudti) AS YTDITAudtiCount,
SUM(InternalApplications) AS YTDInternalApplicationsCount,
SUM(MonitorCasinoIt_OpsProj) AS YTDMonitorCasinoIt_OpsProjCount,
SUM(InternalDatabaseMaintenance_Support) AS YTDInternalDatabaseMaintenance_SupportCount,
SUM(SecurityAdminstration) AS YTDSecurityAdminstrationCount,
SUM(Investigations) AS YTDInvestigationsCount,
SUM(DesktopSupport) As YTDDesktopSupportCount,
SUM(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM ITDepartment
WHERE [Start_Date] <DATEADD(mm,1,Date)
)t1

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:08:27

use DATENAME(mm,Date) to get month names as January,February,...rather than numbers 1,2..

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-29 : 14:10:44

THANK YOU SO MUCH AGAIN Viskah16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:14:45

no problem... you're welcome

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 14:35:20

You shouldn't be doing this inside the stored procedure even though it is possible, but rather format the returned data in your application, which is the presentation layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-29 : 17:35:34

quote:
Originally posted by tkizer

You shouldn't be doing this inside the stored procedure even though it is possible, but rather format the returned data in your application, which is the presentation layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I agree but when I tried that, it didnt work for some reason. I will try again cause thats what I tried to do first

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 17:46:22

You shouldn't attept to fix something in SQL just because it didn't work in the application. What programming language are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-29 : 18:57:22

its an ADP Access 2007

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 18:59:43