在存储的过程中修改以显示日期格式 [英] modification in a stored proc for displaying date format

问题描述

亲爱的所有人，

我有以下存储的proc:

Dear All,

I have the following stored proc:

ALTER proc [dbo].[BINDDAYS] --3
(
@Month BIGINT
)
as
begin
set nocount on

declare @temp table
(
RowId BIGINT IDENTITY,
NewDate nvarchar(50)
)

declare @NewDate datetime,@MAX bigint,@MIN bigint

select @NewDate=CAST(CAST(@Month AS VARCHAR(10))+'/01/2011' 
AS DATETIME)
select @MAX=DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@NewDate)+1,0))),@MIN=1


WHILE(@MIN<=@MAX)
BEGIN
SET @NewDate=CAST(CAST(@Month AS VARCHAR(10))+'/'+CAST(@MIN 
AS VARCHAR(10))+'/2011' AS DATETIME)
INSERT INTO @temp(NewDate) SELECT CONVERT(NVARCHAR(50),@NewDate,103)
SET @MIN=@MIN+1
END

select RowId,NewDate FROM @temp

end

在这里，我可以显示特定月份的所有天数(您可以看到月份号即3作为参数)为1/3/2011,2/3/2011 ...... 31/3 /2011.

但是现在我想将"day"作为参数传递并获取日期格式.
例如:如果我通过24，则应该显示24/1/2011,24/02/2011 ...... 24/12/2011.

任何帮助或建议都非常可观...

问候，

Here, I am able to display all the days for a specific month(you can see the month no. i.e.,3 as a parameter) as 1/3/2011,2/3/2011......31/3/2011.

But now I want to pass the "day" as a parameter and get the date format.
Ex: If I pass 24, then it should display 24/1/2011,24/02/2011......24/12/2011.

Any help or suggestion greatly appreciable...

Regards,

推荐答案

我会说摆脱你已经拥有的东西.您在那里有硬编码的字符串！！

I''d say get rid of what you''ve got already. You''ve got hardcoded strings in there!!

CREATE PROCEDURE  GetAllDaysForMonth

	(
		@Date			DATETIME
	)

AS

SET NOCOUNT ON

DECLARE @CurrentDate DATETIME
DECLARE @FirstDate DATETIME 
DECLARE @LastDate DATETIME 

DECLARE @Days TABLE (DateField DATETIME)

--Find first day of the month
SET @FirstDate =  CAST (FLOOR( CAST(DateAdd(Day, 1, @Date - Day(@Date) + 1) -1 AS FLOAT)) AS DATETIME)

--Find last day of the month
SET @LastDate =  CAST (FLOOR( CAST(DateAdd(Month, 1, @Date - Day(@Date) + 1) -1 AS FLOAT)) AS DATETIME)

--Set start of the loop
SET @CurrentDate = @FirstDate

--Loop until the 'last day of the month' and insert into table variable
While @CurrentDate <= @LastDate
  Begin
     Insert Into @Days Values(@CurrentDate)
     Set @CurrentDate = DateAdd(d,1,@CurrentDate)
  End 

--Return all days in the month
Select * From @Days


-- Return any error codes \ Reset the NOCOUNT property.
RETURN @@ERROR
SET NOCOUNT OFF
GO

用法如下...

Usage as follows...

DECLARE @TheDate DATETIME
SET @TheDate = GETDATE()
EXEC [GetAllDaysForMonth] @TheDate

此是使用公用表表达式(CTE)"的Dylan解决方案的修改版(希望您不要介意)

This is modified version of Dylan''s solution (hope you don''t mind) using "common table expression (CTE)"

CREATE PROCEDURE  GetAllDaysForMonth
    (
        @Date           DATETIME
    )
AS
SET NOCOUNT ON

DECLARE @FirstDate DATETIME 
DECLARE @LastDate DATETIME 

--Find first day of the month
SET @FirstDate =  CAST (FLOOR( CAST(DateAdd(Day, 1, @Date - Day(@Date) + 1) -1 AS FLOAT)) AS DATETIME)

--Find last day of the month
SET @LastDate =  CAST (FLOOR( CAST(DateAdd(Month, 1, @Date - Day(@Date) + 1) -1 AS FLOAT)) AS DATETIME)

--Return all days in the month
;WITH DT AS
(
SELECT DateField    = @FirstDate
UNION ALL
SELECT  DateField+1
FROM    DT
WHERE   DateField   < @LastDate
)
SELECT  *
FROM    DT

好，最后一个...！

您只需将day传递给+，它将处理所有可能的日期排列.例如，如果您将31作为该日期传递给它，它将只获得没有31天的月份中的最后可用日期"

OK, final one...!

You only pass day to this + it will handle all possible date permutations. e.g if you pass 31 to this as date, it will just get the ''last date available'' in months that don''t have 31 days

CREATE PROCEDURE  [dbo].[GetAllMonthsForDay]
	(
		@Day	INT
	)
AS

SET NOCOUNT ON

DECLARE @CurrentDate DATETIME
DECLARE @FirstDate DATETIME 
DECLARE @LastDate DATETIME 

DECLARE @Today DATETIMe
DECLARE @Months TABLE (DateField DATETIME)
DECLARE @Year INT

IF (@Day < 1 OR @Day > 31)
BEGIN
	RAISERROR('Invalid day specified for date function', 16, 1)
	RETURN
END

SET @Today = GETDATE()

--The day in the date
SET @Year = DATEPART(yyyy, @Today )
SET @FirstDate = CAST(CAST(1 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
SET @LastDate = CAST(CAST(12 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)

--Set start of the loop
SET @CurrentDate = @FirstDate

--Loop until the 'last day of the month' and insert into table variable
While @CurrentDate <= @LastDate
  Begin
     Insert Into @Months Values(@CurrentDate)

     --If date has changed, it could be because we've tried to increment a value that is not valid
     --e.g 31st won't exist in February	
     IF (DATEPART(dd, @CurrentDate) != @Day)
     BEGIN
         Set @CurrentDate = DateAdd(m,1,@CurrentDate)
         SET @CurrentDate = CAST(CAST(DATEPART(mm, @CurrentDate) AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
     END
	 ELSE
	 BEGIN
         Set @CurrentDate = DateAdd(m,1,@CurrentDate)
     END     
  End 

--Return all days in the month
Select * From @Months


-- Return any error codes \ Reset the NOCOUNT property.
RETURN @@ERROR
SET NOCOUNT OFF

这篇关于在存储的过程中修改以显示日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！