CODEWITHSUNDEEP
sql-server
user222427
user222427

Reputation:

days of the month

I know this sounds ridiculous... I know... but I want to be able to select the first 7 days of the month and get a result like this.

this is what I have:

SELECT row_number () OVER (ORDER BY DateD), *
  FROM (SELECT DATENAME (dw, GETDATE ()) AS 'DateName', getdate () AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 1) AS 'DateName',
               getdate () + 1 AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 2) AS 'DateName',
               getdate () + 2 AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 3) AS 'DateName',
               getdate () + 3 AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 4) AS 'DateName',
               getdate () + 4 AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 5) AS 'DateName',
               getdate () + 5 AS 'DateD'
        UNION
        SELECT DATENAME (dw, GETDATE () + 6) AS 'DateName',
               getdate () + 6 AS 'DateD') queryTable

Upvotes: 1

Views: 338

Answers (5)

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

Here is a simpler approach to getting @n days in the current month. If you need it for a different month, just replace GETDATE() with a variable that represents any datetime value within the month you want.

;WITH x AS 
(
  SELECT TOP (@n) n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
),
y(d,n) AS
(
  SELECT DATEADD(DAY, n-1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),
   n FROM x
)
SELECT 
  RowNumber = n, 
  [Weekday] = DATENAME(WEEKDAY, d), 
  [Date_In_Ambiguous_Format] = CONVERT(CHAR(10), d, 101),
  [Date_In_Proper_DataType] = d
FROM y;

Please see this blog series about generating sets without loops and without repeating code:

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2
http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

Using SQL Server, you can query master..spt_values to get a list of numbers (not an entire list of numbers, but definitely 1 to 7):

SELECT 
   row_number () OVER (ORDER BY Number) as Row, 
   DATENAME (dw,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GetDate())-Number),GetDate()),101) ) AS DtName,
   CONVERT(VARCHAR, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GetDate())-Number),GetDate()),101), 101) As Dt
FROM (SELECT Number FROM master..spt_values WHERE Number BETWEEN 1 AND 7 AND Type = 'P') t

Just another approach -- have fun with it! And here is the Fiddle.

Good luck.

Upvotes: 0

Matt Smucker
Matt Smucker

Reputation: 5244

WITH Dates AS
(
    SELECT DATEADD(DAY, - (DATEPART(DAY, GETDATE()) - 1), CONVERT(VARCHAR, GETDATE(), 101)) as DateDay
    UNION ALL
    SELECT DateDay + 1
    FROM Dates
    WHERE DATEPART(DAY, DateDay) < 7
)

SELECT
    DATEPART(DAY, DateDay),
    DATENAME(DW, DateDay),
    DateDay
FROM Dates

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

You can use this recursive CTE:

WITH dayscte ( d ) 
     AS (SELECT DATEADD(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS d 
         UNION ALL 
         SELECT Dateadd(d, 1, d) 
         FROM   dayscte 
         WHERE  Datepart(day, d) < 7)  -- number of days you want
SELECT d, 
       Datepart(wk, d) AS week_number, 
       Datename(dw, d) AS day_name, 
       Datename(m, d)  AS month_name, 
       Datename(q, d)  AS [quarter] 
FROM   dayscte 
OPTION (maxrecursion 800);

Demo

Upvotes: 0

Taryn
Taryn

Reputation: 247850

Here is a really ugly query that gives you the result:

SELECT row_number () OVER (ORDER BY DateD), *
FROM 
(
    SELECT DATENAME (dw, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateName', 
        DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 2, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 2, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 3, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 3, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 4, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 4, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 5, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 5, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
    UNION
    SELECT DATENAME (dw, DateAdd(day, 6, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))) AS 'DateName',
           DateAdd(day, 6, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS 'DateD'
) queryTable

See SQL Fiddle with Demo

Upvotes: 1

Related Questions