如何将日期表示为水平？ [英] how to represent date as horizontaly?

问题描述

大家好...我正在尝试使用存储过程来表示按用户选择的日期。查询运行成功但日期不会表示为水平格式。它显示为垂直格式。然后我如何横向代表这个日期。

  USE  [BGIndia_Current] 
  GO  
  / *   * ****对象：StoredProcedure [dbo]。[sp_GetAllDays_as_month]脚本日期：12/19/2015 10:57:45 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
   -    =============================================  
   -   作者： 
   -   创建日期： < 19/12 / 2015>  
   -   描述： 
   -    ====================== =======================  
   -    sp_GetAllDays_as_month '01 / 01/2015'，'31 / 01/2015' 
 创建  PROCEDURE  [dbo]。[sp_GetAllDays_as_month] 
  @ StartingDate   varchar （ 15 ），
  @ EndingDate   varchar （ 15 ）
  AS  
  BEGIN  
   -   添加SET NOCOUNT ON以防止额外的结果集来自 
   -   干扰SELECT语句。 
   -    convert（datetime，'23 / 10/2016'，103） 
  SET   NOCOUNT   ON ; 
   -   在此处插入程序语句 
  DECLARE   @ Year   AS   INT ，
  @ FirstDateOfYear   DATETIME ，
  @ LastDateOfYear   DATETIME  
   -   您可以将@year更改为您想要的任何年份 
  SELECT   @年 =  2015  
  SELECT   @FirstDateOfYear  =  CONVERT （ datetime ， @ STARTINGDATE ， 103 ）  -    DATEADD（yyyy，@ Year  -  1900， 0） 
  SELECT   @ LastDateOfYear  =  CONVERT （ datetime ， @ EndingDate ， 103 ）
   -   创建查询以准备年度数据 
;  WITH  cte  AS （
 
  SELECT   1   AS  DayID， @ FirstDateOfYear   AS  FromDate，DATENAME（dw， @ FirstDateOfYear ） AS 日期名称 UNION   ALL  
  SELECT  cte.DayID +  1   AS  DayID，DATEADD（d， 1 ，cte.FromDate）， DATENAME（dw，DATEADD（d， 1 ，cte.FromDate）） AS  Dayname 
 < span class =code-keyword> FROM  cte 
  WHERE  DATEADD（d， 1 ,cte.FromDate）<  @ LastDateOfYear ）
  SELECT  ' '  as 名称，' '  as 指定，
 ' < span class =code-string> P'  as  MA_LeaveTypeName，' '  as  From_date，' '  as  To_date，FromDate  AS  日期，日期名称 FROM  CTE   -    WHERE DayName IN（'星期六'，'星期日'） 
 选项（MaxRe） cursion  370 ）
  END  

解决方案

稍微修改了你的代码以获得一个空格分隔的日子。看看

  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
   -    === ==========================================  
   -   作者： 
   -   创建日期：< 19/12 / 2015>  
   -   描述： 
   -    ======= ======================================  
   -    sp_GetAllDays_as_month '01 / 01/2015'，'31 / 01/2015' 
  ALTER  程序 [dbo]。[sp_GetAllDays_as_month] 
  @ StartingDate   varchar （ 15 ），
  @ EndingDate   varchar  （ 15 ）
  AS  
  BEGIN  
   -   添加SET NOCOUNT ON以防止来自 $的额外结果集b $ b   -   干扰SELECT语句。 
   -    convert（datetime，'23 / 10/2016'，103） 
  SET   NOCOUNT   ON ; 
   -   在此处插入程序语句 
  DECLARE   @ Year   AS   INT ，
  @ FirstDateOfYear   DATETIME ，
  @ LastDateOfYear   DATETIME  
   -   您可以将@year更改为您想要的任何年份 
  SELECT   @年 =  2015  
  SELECT   @FirstDateOfYear  =  CONVERT （ datetime ， @ STARTINGDATE ， 103 ）  -    DATEADD（yyyy，@ Year  -  1900， 0） 
  SELECT   @ LastDateOfYear  =  CONVERT （ datetime ， @ EndingDate ， 103 ）
 
  DECLARE   @ ConcatString   VARCHAR （ 4000 ）
   -   创建查询以准备年份数据 
;  WITH  cte  AS （
 
  SELECT   1   AS  DayID， @ FirstDateOfYear   AS  FromDate，DATENAME（dw， @ FirstDateOfYear ） AS 日期名称 UNION  所有 
  SELECT  cte.DayID +  1   AS  DayID，DATEADD（d， 1 ，cte.FromDate），DATENAME（dw，DATEADD（d， 1 ，cte.FromDate）） AS 日期名称
  FROM  cte 
  WHERE  DATEADD（d， 1 ，cte.FromDate）<  @ LastDateOfYear ）
   -    SELECT''作为名称，''作为名称， 
   -   'P'作为MA_LeaveTypeName，'' as_date，''作为To_date，FromDate AS日期，日期 
   -    FROM CTE  -  -  WHERE DayName IN（'Saturday'，'Sunday'） 
  SELECT   @ ConcatString  =  COALESCE （ @ ConcatString  + ' '，' '）+ cast （DayID  as   varchar （ 2 ）） 
  FROM  CTE 
  OPTION （MaxRecursion  370 ）
 
  SELECT   @ ConcatString  
 
 
  END  

Hi all...i am trying to use stored procedure for representing date as per selected by user. query run succesfuly but date will not represented as horizontal format..it shows as vertically format. then how i represent this date horizontally.

USE [BGIndia_Current]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetAllDays_as_month]    Script Date: 12/19/2015 10:57:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: <19/12/2015>
-- Description:	
-- =============================================
-- sp_GetAllDays_as_month '01/01/2015', '31/01/2015'
Create  PROCEDURE [dbo].[sp_GetAllDays_as_month] 
	@StartingDate varchar(15),
	@EndingDate varchar(15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- convert(datetime, '23/10/2016', 103)
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    DECLARE @Year AS INT,
    @FirstDateOfYear DATETIME,
    @LastDateOfYear DATETIME
    -- You can change @year to any year you desire
    SELECT @year = 2015
    SELECT @FirstDateOfYear = CONVERT(datetime,@StartingDate,103) -- DATEADD(yyyy, @Year - 1900, 0)
    SELECT @LastDateOfYear = CONVERT(datetime,@EndingDate,103)
    -- Creating Query to Prepare Year Data
    ;WITH cte AS (
     
    SELECT 1 AS DayID, @FirstDateOfYear AS FromDate, DATENAME(dw, @FirstDateOfYear) AS Dayname UNION ALL
			SELECT cte.DayID + 1 AS DayID, DATEADD(d, 1 ,cte.FromDate), DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname 
			FROM	cte
			WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear)
	SELECT '' as Name, '' as Designation, 
		'P' as MA_LeaveTypeName, '' as From_date,  '' as To_date, FromDate AS Date, Dayname FROM CTE  -- WHERE DayName IN ('Saturday','Sunday') 
	OPTION (MaxRecursion 370)
END

解决方案

Have modified your code a bit to get a space separated days. Have a look

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: <19/12/2015>
-- Description:	
-- =============================================
-- sp_GetAllDays_as_month '01/01/2015', '31/01/2015'
ALTER  PROCEDURE [dbo].[sp_GetAllDays_as_month] 
	@StartingDate varchar(15),
	@EndingDate varchar(15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- convert(datetime, '23/10/2016', 103)
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    DECLARE @Year AS INT,
    @FirstDateOfYear DATETIME,
    @LastDateOfYear DATETIME
    -- You can change @year to any year you desire
    SELECT @year = 2015
    SELECT @FirstDateOfYear = CONVERT(datetime,@StartingDate,103) -- DATEADD(yyyy, @Year - 1900, 0)
    SELECT @LastDateOfYear = CONVERT(datetime,@EndingDate,103)
    
    DECLARE   @ConcatString VARCHAR(4000)
    -- Creating Query to Prepare Year Data
    ;WITH cte AS (
     
    SELECT 1 AS DayID, @FirstDateOfYear AS FromDate, DATENAME(dw, @FirstDateOfYear) AS Dayname UNION ALL
			SELECT cte.DayID + 1 AS DayID, DATEADD(d, 1 ,cte.FromDate), DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname 
			FROM	cte
			WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear)
	--SELECT '' as Name, '' as Designation, 
	--	'P' as MA_LeaveTypeName, '' as From_date,  '' as To_date, FromDate AS Date, Dayname 
	--FROM CTE  -- WHERE DayName IN ('Saturday','Sunday') 
	SELECT   @ConcatString = COALESCE(@ConcatString + ' ', '') + cast(DayID as varchar(2))
	FROM     CTE 
	OPTION   (MaxRecursion 370)
	
	SELECT @ConcatString
	

END

这篇关于如何将日期表示为水平？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！