开发者社区> geovindu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

display month as a calendar using sql

简介: ---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)) 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL中date、datetime、timestamp、time、year的区别
MySQL中date、datetime、timestamp、time、year的区别
0 0
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-...
742 0
sql 的 DATE_FORMATE()函数
定义和用法 DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 语法 DATE_FORMAT(date,format) date 参数是合法的日期。format 规定日期/时间的输出格式。
540 0
SQL Server使用convert对datetime日期数据进行获取
来源:http://database.51cto.com/art/201007/211883.htm 备注:本文的语法讲解确实是比较乱,似乎格式不太严谨。参考时还是以实例验证为准比较好   以下的文章主要描述的是SQL Server使用convert取得datetime日期数据的实际操作流程,在实际操作中用SQL Server数据库中用convert来获取datetime日期数据,以下实例包含各种日期格式的转换。
851 0
SQL GETDATE()日期格式化函数
原文: SQL GETDATE()日期格式化函数 Sql Server 中一个非常强大的日期格式化函数 Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONV...
781 0
SQL 中使用CONVERT转日期格式
常常用,常常忘,摘下来做个笔记。原作者不详了。   CONVERT 将某种数据类型的表达式显式转换为另一种数据类型。由于某些需求经常用到取日期格式的不同.现以下可在 SQL Server中 将日期格式化. SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
482 0
sql server使用convert来取得datetime日期数据
sql server使用convert来取得datetime日期数据,以下实例包含各种日期格式的转换 语句及查询结果: Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONVERT(varchar(1...
729 0
+关注
geovindu
读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载