display month as a calendar using sql

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: ---sql server 2005 顯示一個月的數據,如果沒有空的也要顯示出來 declare @T table([geovinddate] Datetime,[workcontent] nvarchar(8),[worker] nvarchar(50)) Insert @T select '2012-8-11',N'遲到','geovindu' union all select
---sql server 2005 顯示一個月的數據,如果沒有空的也要顯示出來
declare @T table([geovinddate] Datetime,[workcontent] nvarchar(8),[worker] nvarchar(50))
Insert @T
select '2012-8-11',N'遲到','geovindu' union all
select '2012-8-12',N'早退','geovin'

;with Date
as
(select cast('2012-08-01' as datetime) Date
union all
select Date+1 from Date where Date+1<'2012-9-1')
select
cast(a.Date as varchar(50))+' '+ DATENAME(weekday, a.Date), [workcontent]=isnull([workcontent],''),[worker]=isnull([worker],'')
from
Date a
left join
@T b on a.Date=b.[geovinddate]


---sql server 2005:display current month as a calendar using sql
;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

---
DECLARE @Year int, @Month int, @LastDay int;
SET @Year = 2013;
SET @Month = 5;
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

---sql server 2000 顯示一個月的數據,如果沒有空的也要顯示出來
declare @d table(geovinddate datetime)
declare @date datetime
set @date='2012-08-01'
while @date<'2012-09-1'
begin
insert @d select @date
set @date=dateadd(dd,1,@date)
end
declare @t table(geovinddate datetime,workcontent varchar(20))
insert @t select '2012-8-11','遲到'
insert @t select '2012-8-12','早退'
select cast(d.geovinddate as varchar(50))+' '+ DATENAME(weekday, d.geovinddate) as '發生日期', isnull(t.workcontent,'') as '備注' from @d d left join @t t on d.geovinddate=t.geovinddate --空值不顯示

---sql server 2000 
DECLARE @Month AS INT  --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT  --Set the YEAR for which you want to generate the Calendar.

DECLARE @StartDate AS DATETIME 
DECLARE @EndDate AS DATETIME 
set  @Month = 5
set  @Year = 2013
set @StartDate= CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
set @EndDate= DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

SELECT
SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
FROM master.dbo.spt_values v
WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate 
AND DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
AND v.type = 'P'
GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate)) 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5月前
|
SQL 存储 关系型数据库
SQL Date 函数
SQL Date 函数
54 3
|
6月前
|
SQL HIVE
Hive中日期处理函数的使用(date_format、date_add、date_sub、next_day)
Hive中日期处理函数的使用(date_format、date_add、date_sub、next_day)
1242 3
|
SQL Oracle 关系型数据库
sql中sysdate 和 current_date 的区别及to_char( tv_date, ‘YYYY-MM-DD‘)当天时间与数据库时间的格式转换与比较
在oracle中current_date与sysdate都是显示当前系统时间, 其结果基本相同,但是有三点区别: 1. current_date返回的是当前会话时间,而sysdate返回的是服务器时间; 2. current_date有时比sysdate快一秒,这可能是四舍五入的结果; 3. 如果修改当前会话的时区,比如将中国的时区为东八区,修改为东九区,则current_date显示的时间为东九区时间, 根据东加西减的原则,current_date应该比sysdate快一小时。
510 0
sql中sysdate 和 current_date 的区别及to_char( tv_date, ‘YYYY-MM-DD‘)当天时间与数据库时间的格式转换与比较
|
SQL 关系型数据库
Postgre SQL date_trunc() 和timestamp
Postgre SQL date_trunc() 和timestamp
113 0
|
API Android开发
Date & Time组件(上)
本节给大家带来的是Android给我们提供的显示时间的几个控件,他们分别是: TextClock,AnalogClock,Chronometer,另外其实还有个过时的DigitalClock就不讲解了! 好的,开始本节内容!
122 0
|
存储 关系型数据库 MySQL
MySQL中date、datetime、timestamp、time、year的区别
MySQL中date、datetime、timestamp、time、year的区别
178 0
|
MySQL 关系型数据库 存储
【MySQL】 DATE_FORMAT() 格式化时间
【MySQL】 DATE_FORMAT() 格式化时间 我们会在项目中遇到这样一种需要,时间存储的时候存的是datetime,也就是年月日,时分秒,但是我们在使用的时候只需要使用年月日的时间,如果我们查询出成千上万条数据,使用代码转化可能会降低效率以及使得代码变得更加繁琐,这时候我们可以考虑在数据源就将其转化。
8600 0
|
SQL MySQL 关系型数据库
mysql DATE_FORMAT(date, format) 函数
DATE_FORMAT(date, format) 函数用法   DATE_FORMAT(date, format) 函数根据format字符串格式化date值。   1.把字符串转为日期格式 实例: SELECT DATE_FORMAT('2017-09-20 08:30:45', '%Y-...
3335 0
|
关系型数据库 MySQL
报错:Incorrect datetime value: '0000-00-00 00:00:00' for column 'login_time' at row 1
报错:Incorrect datetime value: '0000-00-00 00:00:00' for column 'login_time' at row 1
650 0
|
SQL
SQL Server 日期的加减函数: DATEDIFF DATEADD
原文:SQL Server 日期的加减函数: DATEDIFF DATEADD 原文地址:http://blog.csdn.net/xyzqiang/article/details/6577831 SQL Server 日期的加减函数: DATEDIFF    DATEADDDATEDIFF: 返...
2619 0