节假日表holiday
holiday_id holiday_name holiday_time
1 春节 2013-02-11
2 元旦 2014-01-01
3 国庆 2013-10-01
如何得到与当前日期最近的一个节日,并计算还有多少天到
holiday_id holiday_name holiday_time
1 春节 2013-02-11
2 元旦 2014-01-01
3 国庆 2013-10-01
如何得到与当前日期最近的一个节日,并计算还有多少天到
解决方案 »
- 请问这些信息时如何生成的?
- 求一sql语句 ,行转列的,请各位高手指点一下,在线等,着急啊,各位高手们,指点指点呀!
- 想做一个WEB应用程序,数据量能达到1000万,表的键值都采用了GUID,这样表的关联和查询会不会受到影响
- 一个棘手的问题:如何编写存储过程,实现插入多行数据?
- sql server 2008 R2 删除数据表中多列属性重复的的记录
- exec怎么不执行呢?
- 非常奇怪的事务回滚问题
- 【求】明细表和汇总表联合问题!!!
- 服务组的名称可否更改?如何更改!
- 帮写个执行语句,谢谢各位
- 如何传一个参数找出在标示表中存在3条或0记录的视频记录? 有查询示例SQL 谢谢
- 下面的语句出错如何改正
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-02 12:28:07
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[holiday]
if object_id('[holiday]') is not null drop table [holiday]
go
create table [holiday]([holiday_id] int,[holiday_name] varchar(4),[holiday_time] datetime)
insert [holiday]
select 1,'春节','2013-02-11' union all
select 2,'元旦','2014-01-01' union all
select 3,'国庆','2013-10-01'
--------------开始查询--------------------------
SELECT TOP 1 *
FROM (
select * ,DATEDIFF(dd,GETDATE(),holiday_time)diff
from [holiday])a
WHERE diff>0
ORDER BY diff ----------------结果----------------------------
/*
holiday_id holiday_name holiday_time diff
----------- ------------ ----------------------- -----------
3 国庆 2013-10-01 00:00:00.000 60
*/
WITH a1 (holiday_id,holiday_name,holiday_time) AS
(
SELECT 1,'春节','2013-02-11' UNION all
SELECT 2,'元旦','2014-01-01' UNION all
SELECT 3,'国庆','2013-10-01'
)
SELECT TOP 1 holiday_name,holiday_time,DATEDIFF(dd,GETDATE(),holiday_time) diff_days
FROM a1
WHERE holiday_time>=CONVERT(CHAR(10),GETDATE(),20)
ORDER BY holiday_time ASC
SELECT TOP(1)
*,
相差天数 = DATEDIFF(DAY, GETDATE(), holiday_time)
FROM holidya
WHERE holiday_time > CONVERT(CHAR(10), GETDATE(), 120) --可能会相差0天
ORDER BY 相差天数--过去的节日也算数
SELECT TOP(1) WITH TIES --可能存在2个相同间隔的点
*,
相差天数 = ABS(DATEDIFF(DAY, GETDATE(), holiday_time))
FROM holidya
ORDER BY ABS(DATEDIFF(DAY, GETDATE(), holiday_time))
where DATEDIFF(DAY,GETDATE(),holiday_time)>=0 order by DATEDIFF(DAY,GETDATE(),holiday_time)