CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3318137
user3318137

Reputation: 15

How to get the data for the current week from MSSQL

I wanted to know how I can get the records for the current week.

The query I am using is :

DECLARE @TableX TABLE

([Date] DATETIME)

INSERT INTO @TableX

SELECT '2014-2-17' UNION ALL

SELECT '2014-2-18' UNION ALL

SELECT '2014-2-19' UNION ALL

SELECT '2014-2-20' UNION ALL

SELECT '2014-2-21'


SELECT * FROM @TableX

WHERE Date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND  Date <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)

The query I have wrote doesn't produce the data correctly ? Can anyone please tell me what is wrong in the query.

The records I get from this query is :

2014-02-17 00:00:00.000
2014-02-18 00:00:00.000

Upvotes: 1

Views: 2693

Answers (4)

Pablo Contreras
Pablo Contreras

Reputation: 588

simplest way i can suggest to get a date's week is this>

select datepart(wk, [date to test]) as field

hope it helps!

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try using CTE. Below query returns 7 days of week considering Sunday as week start day.

;WITH t 
     AS (SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), -1) AS WeekD, 
                1                                           cnt 
         UNION ALL 
         SELECT weekd + 1, 
                cnt + 1 
         FROM   t 
         WHERE  cnt + 1 < 8) 
SELECT CONVERT(NVARCHAR(20), weekd, 106) WeekDate, 
       Datename(dw, weekd)               Name 
FROM   t

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this proc

Create PROCEDURE getAllDaysBetweenTwoDate
(
@FromDate DATETIME,    
@ToDate DATETIME
)
AS
BEGIN

    DECLARE @TOTALCount INT
    SET @FromDate = dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)
    SET @ToDate = dateadd(dd,((datediff(dd,-53684,getdate())/7)*7)+6,-53684)
    Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

    WITH d AS 
            (
              SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
                OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
              FROM sys.all_objects
            )
        SELECT AllDays From d

    RETURN 
END
GO

Upvotes: 0

Vasile
Vasile

Reputation: 134

This will get all reacords with same week number and same year.

select * from cal where date_format(cal_date,'%v') = date_format(now(),'%v') and date_format(cal_date,'%Y')=date_format(now(),'%Y');

Upvotes: 1

Related Questions