SQL Server display current month as a calendar using sql

bvjveswy 于 2023-05-05 发布在其他

关注(0)|答案(7)|浏览(141)

How to write a t-sql to display current month in calendar format?

the output should be like

sun  mon  tues  wed  thur  fri  sat
           1      2     3     4    5
6    7     8      9     10    11   12
13   14    15     ................

should give the current month calendar

This is what I have so far

declare @start int
declare @day int
declare @space varchar(100)
declare @nodays int
select @start=1
select @space=''
select @nodays=datediff(dd,getdate(),dateadd(mm,1,getdate()))
select @day=case datename(dw,cast(year(getdate()) as varchar(10)) + '-' +cast(month(getdate()) as varchar(10)) + '-' +cast(day(getdate()) as varchar(10))) when 'Sunday' then 1 when 'Monday' then 2 when 'tuesday' then 3 when 'wednesday' then 4 when 'thursday' then 5 when 'friday' then 6 when 'saturday' then 7 end
while(@start<=@nodays)
    if(@day=1)
        if(len(@space)=0)
            select @space=cast(@start as varchar(10))
        else
            select @space=@space+' '+
            case when len(cast(@start as varchar(10)))=1 
            then ' ' 
            else '' 
            end +
            cast(@start as varchar(10))
    else
        if(len(@space)=0)
            select @space=@space+ 
            replicate(char(10),@start) +
            case when len(cast(@start as varchar(10)))=1 
            then ' ' 
            else '' 
            end +
            cast(@start as varchar(10))
        else
            select @space=@space+char(10) + case when len(cast(@start as varchar(10)))=1 then ' ' else '' end +cast(@start as varchar(10))
    select @start=@start+1
print @space+char(10)

sql-server

来源：https://stackoverflow.com/questions/5152212/display-current-month-as-a-calendar-using-sql

7条答案

按热度按时间

aiazj4mn1#

You can change the getdate() to a local declared variable if you like.

;with monthDates
as
(
    select  DATEADD(month, datediff(month, 0, getdate()),0) as d
            ,DATEPART(week, DATEADD(month, datediff(month, 0, getdate()),0)) as w
    union all
    select  DATEADD(day, 1, d)
            ,DATEPART(week, DATEADD(day, 1, d))
    from monthDates
    where d < DATEADD(month, datediff(month, 0, getdate())+1,-1)
)

select  max(case when datepart(dw, d) = 1 then datepart(d,d) else null end) as [Sun]
        ,max(case when datepart(dw, d) = 2 then datepart(d,d) else null end) as [Mon]
        ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tue]
        ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wed]
        ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thu]
        ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Fri]
        ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Sat]
from monthDates
group by w

赞(0）回复(0）举报 2023-05-05

ljo96ir52#

select DATEPART(MONTH,getdate()) -- gets month number

select Datename(month,getdate()) -- gets month name

赞(0）回复(0）举报 2023-05-05

7qhs6swi3#

I don't know what you actually are trying to do here. Printing the calender month with T-SQL is probably not what you want to do.

Here is a query that gives you all dates within the current month as a result set. If you just want the day number of the month (like you did) you can use datepart(d, [Date]) to get that.

;with cteLim as
(
  select
    dateadd(m, datediff(m, 0, getdate()), 0) as FirstDay,
    dateadd(m, datediff(m, 0, getdate())+1, 0)-1 as LastDay
),
cteDays as
(
  select FirstDay as [Date]
  from cteLim
  union all
  select cteDays.[Date] + 1 as [Date]
  from cteDays
    inner join cteLim
      on cteDays.[Date] < cteLim.LastDay
)    
select [Date]
from cteDays

Same function but split result to as "calender"

;with cteLim as
(
  select
    dateadd(m, datediff(m, 0, getdate()), 0) as FirstDay,
    dateadd(m, datediff(m, 0, getdate())+1, 0)-1 as LastDay
),
cteDays as
(
  select FirstDay as [Date]
  from cteLim
  union all
  select cteDays.[Date] + 1 as [Date]
  from cteDays
    inner join cteLim
      on cteDays.[Date] < cteLim.LastDay
)    
select
  datepart(iso_week, [Date]) as [Week],
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 2) as Mon,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 3) as Tue,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 4) as Wed,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 5) as Thu,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 6) as Fri,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 7) as Sat,
  (select datepart(d, D2.[Date])
   from cteDays as D2
   where
    datepart(iso_week, D2.[Date]) = datepart(iso_week, D1.[Date]) and
    datepart(dw, D2.[Date]) = 1) as Sun
from cteDays as D1
group by datepart(iso_week, [Date])

Result

Week        Mon         Tue         Wed         Thu         Fri         Sat         Sun
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
9           NULL        1           2           3           4           5           6
10          7           8           9           10          11          12          13
11          14          15          16          17          18          19          20
12          21          22          23          24          25          26          27
13          28          29          30          31          NULL        NULL        NULL

赞(0）回复(0）举报 2023-05-05

yqlxgs2m4#

Forget your 'procedural code to fabricate a calendar on the fly' approach: SQL is a declarative language so instead think in terms of set-based solutions.

Create a permanent Calendar table containing all past and future dates you are likely to ever need (should only amount to mere tens of thousands of rows) then query this table when required using CURRENT_TIMESTAMP .

See Why should I consider using an auxiliary calendar table?

赞(0）回复(0）举报 2023-05-05

hs1rzwqc5#

here a link for : Creating a Calendar in a single SQL statement

SELECT LPAD(MONTH, 20 - (20 - LENGTH(MONTH)) / 2) MONTH, "Sun", "Mon", "Tue", "Wed",     "Thu", "Fri", "Sat"
  FROM (SELECT TO_CHAR(dt, 'fmMonthfm YYYY') MONTH,
               TO_CHAR(dt + 1, 'iw') week,
               MAX(DECODE(TO_CHAR(dt, 'd'), '1', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Sun",
               MAX(DECODE(TO_CHAR(dt, 'd'), '2', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Mon",
               MAX(DECODE(TO_CHAR(dt, 'd'), '3', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Tue",
               MAX(DECODE(TO_CHAR(dt, 'd'), '4', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Wed",
               MAX(DECODE(TO_CHAR(dt, 'd'), '5', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Thu",
               MAX(DECODE(TO_CHAR(dt, 'd'), '6', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Fri",
               MAX(DECODE(TO_CHAR(dt, 'd'), '7', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Sat"
          FROM (SELECT TRUNC(SYSDATE, 'y') - 1 + ROWNUM dt
                  FROM all_objects
                  WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) -
                       TRUNC(SYSDATE, 'y'))
         GROUP BY TO_CHAR(dt, 'fmMonthfm YYYY'), TO_CHAR(dt + 1, 'iw'))
 ORDER BY TO_DATE(MONTH, 'Month YYYY'), TO_NUMBER(week);

You could add some clauses to specify the month

赞(0）回复(0）举报 2023-05-05

63lcw9qa6#

DECLARE @Year int, @Month int, @LastDay int;
SET @Year = 2011;
SET @Month = 2;
SET @LastDay = DAY(DATEADD(m, 1, CAST(@Year AS varchar) + '-' +
                                 CAST(@Month AS varchar) + '-01') - 1);

WITH dates AS (
  SELECT *, DOW = DATEPART(WEEKDAY, Date), WN = DATEPART(WEEK, Date)
  FROM (
    SELECT
      Date = CAST(CAST(@Year AS varchar) + '-' +
                  CAST(@Month AS varchar) + '-' +
                  CAST(number AS varchar) AS datetime)
    FROM master..spt_values
    WHERE type = 'P'
      AND number BETWEEN 1 AND @LastDay
  ) s
)
SELECT
  Sun = MAX(CASE days.DOW WHEN 1 THEN dates.Date END),
  Mon = MAX(CASE days.DOW WHEN 2 THEN dates.Date END),
  Tue = MAX(CASE days.DOW WHEN 3 THEN dates.Date END),
  Wed = MAX(CASE days.DOW WHEN 4 THEN dates.Date END),
  Thu = MAX(CASE days.DOW WHEN 5 THEN dates.Date END),
  Fri = MAX(CASE days.DOW WHEN 6 THEN dates.Date END),
  Sat = MAX(CASE days.DOW WHEN 7 THEN dates.Date END)
FROM (SELECT DISTINCT DOW FROM dates) days
  CROSS JOIN (SELECT DISTINCT WN FROM dates) weeks
  LEFT JOIN dates ON weeks.WN = dates.WN AND days.DOW = dates.DOW
GROUP BY weeks.WN

赞(0）回复(0）举报 2023-05-05

c9x0cxw07#

set nocount on;
declare @sql varchar(1000), @dt datetime, @i tinyint;
set @dt = CURRENT_TIMESTAMP;
set @sql =
  'declare @d datetime,@i tinyint,@s char(3);'
+ 'select @d=''' + convert(char(8), dateAdd(dd, 1 - day(@dt), @dt), 112) + ''''
+       ',@i=month(@d)'
+       ',@s=space(3)'
+       ',@d=dateAdd(dd,1-datePart(dw,@d),@d);'
+ 'select ';
set @i = 0;
while @i < 7
    select @sql = @sql + case @i when 0 then '' else ',' end
    + 'case month(dateAdd(dd,' + cast(@i as char(1)) + '+n,@d))'
    +   ' when @i then right(@s+dateName(dd,dateAdd(dd,' + cast(@i as char(1)) + '+n,@d)),3)'
    +   ' else @s end [' + left(dateName(dw, @i + @@DATEFIRST - 1), 3) + ']'
        ,  @i = @i + 1;
set @sql = @sql
+ ' from (select 0 as n union select 7 union select 14 union '
+       'select 21 union select 28 union select 35) v';
print dateName(mm, @dt) + ' ' + dateName(yy, @dt);
exec (@sql);

db<>fiddle

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server display current month as a calendar using sql

7条答案

相关问题

热门标签

最新问答