how to display the months based on date

Question

Post reply

how to display the months based on date

vinayak.v

SSCrazy

Points: 2414
More actions
February 22, 2011 at 11:59 pm

#246104

hi..
this is my SP
USE [TASKDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCmtRpt]
-- Add the parameters for the stored procedure here
@logid int
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Date ,From_Name ,To_Name ,Todays_Task ,Tommorrows_Task ,Status,Comments,Date as months
from TaskSheet inner join
login on TaskSheet .loginID = login.loginID
where login.loginID = @logid
order by Date desc
END
i want to display the months field as jan feb mar based on date how to do...?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Varun R Ten Centuries Points: 1171 More actions · Answer 1

Hi Vinayak,

You can use datepart function to acheive this.

SELECT CASE Datepart(mm, '2011-12-23 12:59:28.253')

WHEN 1 THEN 'january'

WHEN 2 THEN 'February'

WHEN 3 THEN 'March'

WHEN 4 THEN 'April'

WHEN 5 THEN 'May'

WHEN 6 THEN 'June'

WHEN 7 THEN 'july'

WHEN 8 THEN 'August'

WHEN 9 THEN 'September'

WHEN 10 THEN 'October'

WHEN 11 THEN 'November'

WHEN 12 THEN 'December'

END AS [Months]

Varun

http://www.sqlinfo.in

vinayak.v SSCrazy Points: 2414 More actions · Answer 2

vinayak.v

SSCrazy

Points: 2414

February 23, 2011 at 12:52 am

#1290248

Thank you very much..

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Hi

You no need to hardcode the months,we can get them by

Select datename(month,getdate()-30)-- Last Month

Select datename(month,getdate())-- Current Month

Select datename(month,getdate()+30)-- Next Month

need to pass the date, to get the month name

Thanks
Parthi

HowardW One Orange Chip Points: 29109 More actions · Answer 4

parthi-1705 (2/23/2011)
Hi
You no need to hardcode the months,we can get them by
Select datename(month,getdate()-30)-- Last Month
Select datename(month,getdate())-- Current Month
Select datename(month,getdate()+30)-- Next Month
need to pass the date, to get the month name

Don't use -30/+30 as it's not always correct(try it on 31st Jan).

Use DATEADD(Month, n, myDate)

bulbanos Ten Centuries Points: 1049 More actions · Answer 5

I suppose it's for SSRS? I recognize you from that forum.

Then you could use the expression =MonthName(Month(Fields!Date.Value),true) in your report.

My LinkedIn profile

vinayak.v SSCrazy Points: 2414 More actions · Answer 6

thanks for replying..

comming from first..

i have the following fields

date,name,todays task,tomo task comments..

now what i want is i want to display the names as month wise

if i enter the month as jan then it should display only jan month..

if i enter feb the feb month..

in report i have grouped the field by name ..

i want to group by name and month

like in jan month how many people task etc..

how to do it..

this is my SP

USE [TASKDB]

GO

/****** Object: StoredProcedure [dbo].[GetCmtRpt] Script Date: 02/23/2011 15:49:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--exec [GetCmtRpt] 2

-- =============================================

-- Author:vinayak

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[GetCmtRpt]

-- Add the parameters for the stored procedure here

@logid int

AS

BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT Date ,From_Name ,To_Name ,Todays_Task ,Tommorrows_Task ,Status,Comments,

(SELECT CASE Datepart(mm,Date)