How to get last 7days data from User Input date in sqlserver

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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:

SQL

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

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.

Posted 9-Aug-12 20:17pm

Madhugundi

Updated 9-Aug-12 20:45pm

v3

Add a Solution

4 solutions

Solution 2

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:

SQL

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

Posted 9-Aug-12 20:32pm

Wendelius

Solution 1

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.

Posted 9-Aug-12 20:26pm

Philip Stuyck

Solution 4

this can be done in another way

SQL

---user Input
DECLARE @vUserInPut AS DATETIME  = '07/24/2012'

SELECT
      Convert(VARCHAR(20),CreatedOn,101)
     ,Id
FROM 'yeartablename'
WHERE  Convert(VARCHAR(20),CreatedOn,101)
BETWEEN Convert(VARCHAR(20),dateadd(day,-7,@vUserInPut),101)
AND  @vUserInPut + 1

Posted 10-Aug-12 1:40am

mangeshtambare

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Madhugundi · Accepted Answer · 2012-08-09T23:59:00

SQL

;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