2014-11-16

SQL Server: Print out all of the days in this year 


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- PRINT OUT ALL OF THE DAYS IN THIS YEAR
SELECT DOY 
  = DATEADD(DAY, RC, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-01-01')
FROM (
  SELECT RC = X1.N + 10 * X10.N + 100 * X100.N
  FROM (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X1
  CROSS JOIN (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X10
  CROSS JOIN (
    SELECT 0 AS N UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS X100
) AS X
WHERE YEAR(GETDATE()) = YEAR(
  DATEADD(DAY, RC, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-01-01')
)
ORDER BY 1
By at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)