如何从sqlserver中的用户输入日期获取最近7天的数据 [英] How to get last 7days data from User Input date in sqlserver

问题描述

我无法从用户输入日期获取过去7天的数据.也就是说，如果用户输入日期是2012-07-27，我想从数据库中获取数据，例如日期是21jul，22jul，23jul，24jul，25jul，26jul，27jul.如果在某特定日期(25jul)没有订单应为零显示.

我的示例查询如下:

Hi,

I am not able to get last 7days data from user input date. i.e if user input date is 2012-07-27, i want data from database like dates are, 21jul, 22jul, 23jul, 24jul, 25jul, 26jul, 27jul.If no orders on a perticular day(25jul) result should be Zero to display.

my sample query is as follows:

Declare @CurrentDate datetime
    Set @CurrentDate='2012-07-27'
    set @CurrentDate=(select CAST(DAY(@CurrentDate) AS VARCHAR(2))+ ' ' + DATENAME(MM, @CurrentDate)   AS [DD Month])


    Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/'))
    select @CurrentDate

SELECT  count(Id) TotalCount,
                CAST(DAY(CreatedDateTime) AS varchar(2))+ ' ' + DATENAME(MM, CreatedDateTime) ShipDay
        FROM    tablename
        WHERE   CAST(DAY(CreatedDateTime) AS varchar(2))+ ' ' + DATENAME(MM, CreatedDateTime)--day month format
 =@CurrentDate-- if write like getting only 27 jul
        --BETWEEN @CurrentDate-7 and @CurrentDate if write like error will come
        GROUP BY CAST(DAY(CreatedDateTime) AS varchar(2))+ ' ' + DATENAME(MM, CreatedDateTime)
        ORDER BY ShipDay

如何编写Where子句以获取最近7天的数据结果应类似于21jul，22jul，23jul，24jul，25jul，26jul，27jul.

谢谢与问候，
Raghu.

how to write the Where clause to get last 7 days data result is should be like, 21jul, 22jul, 23jul, 24jul, 25jul, 26jul, 27jul.

Thanks&Regards,
Raghu.

推荐答案

同意您应该为参数使用DATE或DATETIME数据类型.之后，SQL标准应该非常简单.像

例如，您可以使用以下命令在SSMS中测试查询:

Agree that you should use either DATE or DATETIME datatypes for you parameters. After that the SQL criteria should be quite simple. Something like

For example you can test the query in SSMS with something like:

declare @dateparam as date
begin
   set @dateparam = GETDATE() - 7
   select * from yourtable where datecolumn > @dateparam
end

开始使用的数据类型错误. SQL中有诸如DateTime数据类型之类的东西，为什么不使用它呢?

使用它的一些代码示例:

You are using the wrong data types to begin with. There is such a thing as a DateTime datatype in SQL why don''t you use that ?

An example of some code using it :

ALTER FUNCTION [dbo].[RekenTerug] 
(
	@EindDatum datetime,
    @Periodes int
)
RETURNS datetime
AS
BEGIN
	DECLARE @ResultVar datetime;
    DECLARE @BeginDatum datetime;
    DECLARE @VerlofPeriodes int;

    -- zet de einddatum naar het begin van een periode
    SELECT @EindDatum = 
      CASE 
        WHEN datepart(dw,@EindDatum) = 1 THEN @EindDatum -4 --zondag, zet op woensdag
        WHEN datepart(dw,@EindDatum) = 2 THEN @EindDatum    --maandag, maandag is ok
        WHEN datepart(dw,@EindDatum) = 3 THEN @EindDatum -1 --dinsdag, zet op maandag
        WHEN datepart(dw,@EindDatum) = 4 THEN @EindDatum    --woensdag, woensdag is ok
        WHEN datepart(dw,@EindDatum) = 5 THEN @EindDatum -1 --donderdag, woensdag is ok
        WHEN datepart(dw,@EindDatum) = 6 THEN @EindDatum -2 --vrijdag, woensdag is ok
        WHEN datepart(dw,@EindDatum) = 7 THEN @EindDatum -3 --zaterdag, woensdag is ok
      END
    
    --Als het aantal periodes dat je moet terugrekenen oneven is,
    --zet dan de begindatum aan het begin van de vorige periode
	if @Periodes % 2 = 1
    begin
      SELECT @Periodes = @Periodes - 1;
      SELECT @BeginDatum = 
        CASE
          WHEN datepart(dw,@EindDatum) = 4 THEN @EindDatum - 2 
          WHEN datepart(dw,@EindDatum) = 2 THEN @EindDatum - 5 
        END
    end
    else SELECT @BeginDatum = @EindDatum

    --reken terug
    SELECT @BeginDatum = @BeginDatum - @Periodes/2*7

    --tel het aantal verlofperiodes dat in het interval [BeginDatum,EindDatum]
    SELECT @VerlofPeriodes = COUNT(*) FROM VerlofRegeling2
    WHERE (BeginDatum BETWEEN @BeginDatum and @EindDatum-1) and 
          (EindDatum BETWEEN @BeginDatum and @EindDatum-1)
    
    SELECT @ResultVar = 
      CASE
        WHEN @VerlofPeriodes = 0 THEN @BeginDatum
        ELSE dbo.RekenTerug(@BeginDatum,@VerlofPeriodes)
      END 
    

	RETURN @ResultVar

END

我已经用粗体表示使用了某些日期计算法.

I''ve indicated in bold where some date artithmatic is used.

;WITH DateList AS
(
	SELECT	CONVERT(DATETIME, @CurrentDate) AS CreateDateTime, 1 AS Cnter
	UNION ALL
	SELECT	DATEADD(dd, -1, CreateDateTime), DateList.Cnter + 1
	FROM	DateList
	WHERE	DateList.Cnter + 1 <= 7
)
SELECT	CAST(DAY(DateList.CreateDateTime) AS VARCHAR(2)) + DATENAME(MM, DateList.CreateDateTime), COALESCE(Temp.TotalCount, 0) AS TotalCount
CAST(DAY(CreatedDateTime) AS varchar(2))+ ' ' + DATENAME(MM, CreatedDateTime)--
FROM		DateList
LEFT JOIN	(
				SELECT	COUNT(Id) TotalCount
				,		CONVERT(VARCHAR(10), CreateDateTime, 112) AS CreateDateTime
				FROM    tablename
				WHERE   CreateDateTime >= DATEADD(dd, -6, @CurrentDate)
				AND		CreateDateTime < DATEADD(dd, 1, @CurrentDate)
				GROUP BY CONVERT(VARCHAR(10), CreateDateTime, 112)
			) Temp
ON			CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.CreateDateTime

这篇关于如何从sqlserver中的用户输入日期获取最近7天的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！