基础练习,单表查询

1.重临 每月最终一天订单

本内容是本人上学tsql二〇〇八的开卷笔记

[转载]sql datepart ,dateadd,datediff,DateName函数

 (2011-09-13
15:16:56)

图片 1转载

标签: 

转载

分类: SQL语句(oracle,sql)

详细!!

初稿地址:sql datepart ,dateadd,datediff,DateName函数作者:蓝天碧海

  1. 一时一刻系统日期、时间
    select getdate()

  2. dateadd 在向钦命日期加上后生可畏段时间的底蕴上,再次回到新的 datetime 值
    比如说:向日期加上2天
    select dateadd(day,2,’2004-10-15′) –返回:2004-10-17 00:00:00.000

  3. datediff 重临跨八个钦赐日期的日子和岁月界限数。
    select datediff(day,’2004-09-01′,’2004-09-18′) –返回:17

  4. datepart 再次来到代表钦赐日期的钦赐日期部分的大背头。
    SELECT DATEPART(month, ‘2004-10-15’) –返回 10

  5. datename 重返代表内定日期的钦命日期部分的字符串
    SELECT datename(weekday, ‘2004-10-15’) –返回:星期五

  6. day(), month(),year() –能够与datepart对照一下

select 当前些天子=convert(varchar(10),getdate(),120)
,当今日子=convert(varchar(8),getdate(),114)

select datename(dw,’2004-10-15′)

select 本季度第多少周=datename(week,’二零零三-10-15′)
,今天是周几=datename(weekday,’2001-10-15′)
函数 参数/功能
GetDate( ) 重返系统当下的日子与时光
DateDiff (interval,date1,date2) 以interval 钦命的方法,再次回到date2
与date1四个日子之间的差值 date2-date1
DateAdd (interval,number,date)
以interval钦定的秘籍,加上number之后的日期
DatePart (interval,date)
再次回到日期date中,interval内定部分所对应的整数值
DateName (interval,date)
重回日期date中,interval内定部分所对应的字符串名称

参数 interval的设定值如下:
值 缩 写(Sql Server) Access 和 ASP   说明
Year       Yy
                yyyy   年 1753 ~ 9999
Quarter       Qq     q   季 1 ~ 4
Month       Mm m   月1 ~ 12
Day of year   Dy y    一年的日数,一年中的第几日 1-366
Day       Dd d    日,1-31
Weekday       Dw w    二十七日的日数,十日中的第几日 1-7
Week       Wk ww    周,一年中的第几周 0 ~ 51
Hour       Hh h    时0 ~ 23
Minute       Mi n    分钟0 ~ 59
Second       Ss s    秒 0 ~ 59
Millisecond   Ms –    毫秒 0 ~ 999

access 和 asp
中用date()和now()拿到系统日期时间;其中DateDiff,DateAdd,DatePart也同是能用来Access和asp中,那么些函数的用法也附近

举例:
1.GetDate() 用于sql server :select GetDate()

2.DateDiff(‘s’,’二〇〇五-07-20′,’二〇〇七-7-25 22:56:32′)再次来到值为 514592 秒
DateDiff(‘d’,’2007-07-20′,’贰零零柒-7-25 22:56:32′)重临值为 5 天

3.DatePart(‘w’,’二零零七-7-25 22:56:32′)再次来到值为 2
即星期一(周六为1,周末为7)
DatePart(‘d’,’2007-7-25 22:56:32′)再次来到值为 25即25号
DatePart(‘y’,’二〇〇七-7-25 22:56:32′)重回值为 206即那一年中第206天
DatePart(‘yyyy’,’二零零五-7-25 22:56:32′)重回值为 2007即2007年

SQL Server DATEPART() 函数再次来到 SQLServer datetime 字段的大器晚成有个别。

SQL Server DATEPART() 函数的语法是:
DATEPART(portion, datetime)
中间 datetime 是 SQLServer datetime 字段和局地的称号是下列之生龙活虎: Ms for
Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

详见的申明:

常备,你需求取稳当不久前子和计量一些任何的日期,比方,你的程序也许必要看清二个月的首后天还是最后一天。你们超越55%人民代表大会约都精通如何把日子实行剪切(年、月、日等卡塔 尔(阿拉伯语:قطر‎,然后仅仅用分割出来的年、月、日等位居多少个函数中总结出团结所须要的日期!在此篇作品里,笔者将报告您什么样利用DATEADD和
DATEDIFF函数来计量出在你的主次中只怕您要用到的某些不27日期。
在采纳本文中的例子在此以前,你必得注意以下的难点。大多数也许不是颇有例子在差异的机器上进行的结果可能不雷同,那统统由几时是多个星期的率先天这几个设置决定。第一天(DATEFILANDST卡塔 尔(英语:State of Qatar)设定调控了你的系统应用曾几何时作为四日的率后天。全体以下的例证都以以星期天用作十二日的首后天来创设,也正是首后天设置为7。若是你的率后天设置不肖似,你恐怕需求调动这么些事例,使它和不相同的率后天设置相适合。你能够通过@@DATEFIOdysseyST函数来检查第一天设置。

为了知道那一个事例,大家先复习一下
DATEDIFF和DATEADD函数。DATEDIFF函数总括八个日子之间的小时、天、周、月、年等日子间距总量。DATEADD函数总括三个日期通过给时间间距加减来收获叁个新的日子。要询问更加多的DATEDIFF和DATEADD函数以致时光间隔可以翻阅微软联机扶持。

利用
DATEDIFF和DATEADD函数来计量日期,和自然从眼后日子调换成你须求的日期的虚构方法有个别分裂。你一定要从时间隔断这么些上边来伪造。举例,从今以往时此刻日子到您要获取的日期之间有稍许时间隔开,也许,从后天到某一天(比如1905-1-1卡塔尔之间有些许时间距离,等等。通晓什么重点于岁月间距有利于你轻易的理解本身的比不上的日子总结例子。

二个月的首后天

第三个例子,作者将报告您怎么从当下日子去上个月的末尾一天。请小心:那些例子以至那篇随笔中的别的例子都将只行使DATEDIFF和DATEADD函数来总计我们想要的日子。每三个例子都将因而测算但前的时间隔离,然后进行加减来得到想要总括的日子。

那是计量一个月第一天的SQL 脚本:
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

小编们把这么些讲话分开来看看它是什么职业的。最中心的函数是getdate(),大多数人都领会那个是回去当前的日期和岁月的函数。下贰个试行的函数
DATEDIFF(mm,0,getdate())是计量当前日子和“一九〇五-01-01
00:00:00.000”那一个日期之间的月数。记住:时代和岁月变量和纳秒同样是从“一九〇二-01-01
00:00:00.000”开首揣摸的。那就是干吗您能够在DATEDIFF函数中内定第两个日子表达式为“0”。下二个函数是DATEADD,扩张当后天子到“一九〇〇-01-01”的月数。通过增添预订义的日子“一九零一-01-01”和眼前不久子的月数,大家能够获得上一个月的首后天。别的,计算出来的日子的时辰部分将会是“00:00:00.000”。

这一个计算的技巧是先总计当今天子到“1900-01-01”的岁月间距数,然后把它加到“一九〇五-01-01”上来拿到非常的日子,那些本领能够用来计量比比较多不风华正茂的日子。下多少个例证也是用这一个技能从近年来日期来发生不一样的日子。

本周的礼拜意气风发

这里作者是用周(wk)的小时间距来测算几时是本周的周四。

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

一年的第一天

今天用年(yy)的时光间距来展现这个时候的首后天。

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

季度的首后天

固然你要计算那些季度的首后天,这一个事例告诉你该怎么办。

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

当日的深夜

早已供给通过getdate()函数为了再次回到时间值截掉时间有个别,就能够假造到目几日前期是还是不是在半夜。要是这样,这些例子使用DATEDIFF和DATEADD函数来拿到半夜三更的时间点。

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

深远DATEDIFF和DATEADD函数总计

你能够知晓,通过应用简单的DATEDIFF和DATEADD函数计算,你能够开采许多不风流潇洒的或是有含义的日子。

最近停止的富有例子只是豆蔻年华味计算当前的年华和“一九零一-01-01”之间的小运间距数量,然后把它加到“一九零一-01-01”的年月间距上来总计出日期。假定你改改时间隔绝的数据,恐怕使用不相同的时间间距来调用DATEADD函数,可能减弱时间隔断实际不是增加,那么通过那么些小的调节你能够开采和多分裂的日期。

此处有多个例证使用此外二个DATEADD函数来估测计算最后一天来分别替换DATEADD函数前后四个时刻间隔。

过阵子的最终一天

这是叁个测算上月最后一天的事例。它通过从三个月的结尾一天那么些例子上压缩3阿秒来得到。有好几要牢牢记住,在Sql
Server中时间是正确到3飞秒。那就是为什么自个儿索要减少3阿秒来拿到自己要的日子和岁月。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

计算出来的日子的小运部分含有了八个Sql
Server能够记下的一天的终极每天(“23:59:59:997”)的时刻。

二〇一八年的最终一天

接连下面的事例,为了要赢得2018年的末尾一天,你供给在当年的第一天上减小3皮秒。

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

前段时期的最终一天

前些天,为了拿到本月的结尾一天,小编须求多少修改一下赢得过风流倜傥阵子的最后一天的话语。改过须求给用DATEDIFF比较当前几日子和“一九〇〇-01-01”再次来到的时间隔离上加1。通过加1个月,小编总计出上个月的第一天,然后减去3飞秒,那样就总结出了上月的末梢一天。那是简政放权上月最后一天的SQL脚本。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

现年的末段一天

你未来理应调控这么些的做法,那是测算上一季度最后一天脚本

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

前段时期的率先个周二

好了,以往是最后多个例证。这里自个儿要计算下个月的首先个周风姿浪漫。那是测算的剧本。

select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

在这一个例子里,小编使用了“本周的星期五”的脚本,并作了一丝丝改进。改革的局地是把原先剧本中“getdate()”部分替换来总结前段时间的第6天,在思忖中用前些日子的第6天来替换当不久前期使得总结能够收获前段时期的首先个星期三。

总结

本人愿意那些事例能够在你用DATEADD和DATEDIFF函数计算日期时给您或多或少启迪。通过应用这一个总结日期的时间隔绝的数学方法,作者发觉为了展现四个日子之间间隔的有用历法是有价值的。注意,那只是计量出那一个日子的后生可畏种艺术。要铭记在心,还也有多数办法能够拿走意气风发致的测算结果。若是你有其余的措施,那非常不错,假如你未曾,笔者梦想这个事例能够给您有的启示,当您要用DATEADD和DATEDIFF函数总括你程序也许要用到的日期时。


附录,别的日期管理措施

1)去掉时分秒
declare @ datetime
set @ = getdate() –‘2003-7-1 10:00:00’
SELECT @,DATEADD(day, DATEDIFF(day,0,@), 0)

2卡塔 尔(阿拉伯语:قطر‎展现星期几
select datename(weekday,getdate())

3卡塔 尔(英语:State of Qatar)怎么着获得有个别月的运气
declare @m int
set @m=2 –月份
select datediff(day,’2003-‘+cast(@m as varchar)+’-15′ ,’2003-‘+cast(@m+1
as varchar)+’-15′)
除此以外,得到前些日子命局
select datediff(day,cast(month(GetDate()) as
varchar)+’-‘+cast(month(GetDate()) as varchar)+’-15′
,cast(month(GetDate()) as varchar)+’-‘+cast(month(GetDate())+1 as
varchar)+’-15′)
抑或利用总结上月的最后一天的脚本,然后用DAY函数区最终一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))

4卡塔 尔(英语:State of Qatar)推断是不是闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0)))) when 28 then ‘平年’ else ‘闰年’ end
或者
select case
datediff(day,datename(year,getdate())+’-02-01′,dateadd(mm,1,datename(year,getdate())+’-02-01′))
when 28 then ‘平年’ else ‘闰年’ end

5卡塔尔国一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select
@time=datename(year,getdate())+’-‘+convert(varchar(10),@m)+’-01′
select datediff(day,@time,dateadd(mm,3,@time))

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);

何以是关系模型?

 使用EMONTH 对输入的日子重回月末日期   相符动态条件

若对列成立唯生机勃勃约束,背后中的物理机制也是创办了二个独一索引.

DATEDIFF(month, ‘壹玖玖玖1231’, orderdate)  相差多少月 
从19961231到 orderdate之间先查多少月

SQL语句的逻辑解析顺序是FROM

DATEADD(month, DATEDIFF(month, ‘一九九六1231’, orderdate), ‘19971231’)
中间是加上的月数  最终三个从哪些日子初阶加

WHERE

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');

GROUP BY

三种写法  记住 能够用动态条件 区分记录

HAVING

 

SELECT

  1. 姓氏a上有相像的 

    SELECT empid, firstname, lastname
    FROM HR.Employees
    WHERE lastname LIKE ‘%a%a%’;

OVER

3.对每种用户的订单日期排序

DISTINCT

SELECT custid, orderdate, orderid,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;

TOP

4.口径筛选case

ORDER BY

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE titleofcourtesy
    WHEN 'Ms.'  THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Mr.'  THEN 'Male'
    ELSE             'Unknown'
  END AS gender
FROM HR.Employees;

SELECT empid, firstname, lastname, titleofcourtesy,
CASE 
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;

 倘使SELECT语句中关系到分组,则后边的select order
having的操作对象将是分组。

首先种 是指向找寻  固定的照准  第三种未有可以多字段条件

 全部聚合函数都会忽视NULL值,唯有count(*)除外。

5.在order by 加限定排序条件

包涵order by子句的询问被ANSI称之为游标。表表明式不可能处理游标。

SELECT custid, region
FROM Sales.Customers
ORDER BY
  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

TOP 是TSQL所特有的,用来界定重回的行数或比重。

八个条件 0,1 暗中同意排序法则 通过int 对相应的规格进行排序

开窗函数使用OVE奇骏子句提供窗口作为上下文,对窗口中的风度翩翩组值进行操作,而不是利用GROUP
BY 子句提供的上下文。这样能够并且返回底蕴行的列和聚合列。

ROW_NUMBECRUISER()
函数用于为查询的结果集中的各行分配贰个依次增加的体系号。其逻辑顺序通过OVEEnclave子句中的O奥迪Q5DER
BY语句来张开点名。但纵然OQashqaiDER
By的列不唯意气风发,则系列值也会加多,那样会导致再次来到的值不鲜明。

RANK 和 DENSE_RANK为富有同等逻辑排序值的有着行生成相通的排行。

RANK表示前面有微微行更低的排序值,而DENSE
RANK表示前边有个别许个例外的排序值。

 

能够认为在同意气风发SELECT子句中不同的时间钦赐DISTINCT和ROW NUMBE翼虎是一条最好施行,

因为DISTINCT在此种情形下不起任何意义。

 

SELECT empid,firstname,lastname

FROM HR.Employees

WHERE lastname like N’D%’;

N表示National,用于表示字符串是Unicode数据类型(NCHA本田UR-V或NVARCHAENVISION).

 

SQL中的三值逻辑,假诺想回去region列不对等WA的具有行,如下:

SELECT custid,country,region,city

FROM Sales.Customers

WHERE region<>N’WA’

OR region IS NULL;

 

SQL支持二种字符数据类型:普通字符和Unicode字符。

常常来讲字符使用贰个字节来保存逐个字符,所以限定这么些列所协助的语言只可以是韩文。

 

采用定长的字符串时,SQLSE宝马X5VETucson会预先分配相应的空间,使其无法扩充,它更切合以写入为主的系列,不过那体系型的储存消耗不是最优的,读取的时候只怕要提交更加多的代价。

变长字符的蕴藏空间由实际数据量来支配,可是订正数据的时候恐怕会需求多少移动,超过当前页的限制,所以更新时的频率相对会低一些。

 

SELECT PATINDEX(‘%[0-9]%’,’abcd123efgh’);

能够应用Replace来计量某字符在字符串中现身的次数,方法是先把它替换到空,然后比对前后的差值再除以要合营的字符串的尺寸就能够了。

SELECT STUFF(‘A2DEFG’,2,1,’BC’) 返回:ABCDEFG

 

DateTimeoffset 有时区部分能够举办识别

DateTime2 精度可控,何况辅助的节制从0001带头到9999.

 

将字符串文字转成日期类型时,若无一点点名时间,sqlserver则暗中同意用晚上时光作为其时间值.

在非常多场合下,在过滤条件中对列举行函数化管理,极有非常大大概招致相应列上的目录超小概运用,裁减查询效能,实施方案是应用等值的节制查询.举例:

SELECT orderid FROM sales.Orders WHERE YEAR(ORDERDATE)=2007

:SELECT orderid FROM sales.Orders WHERE ORDERDATE >=’20070101′ AND
ORDERDATE<‘20080101’

 

SELECT

current_timestamp as [current_timestamp],

GETDATE() as [getdate],

getutcdate() as [getutcdate],

SYSDATETIME() as [sysdatetime],

SYSUTCDATETIME() as [sysutcdatetime],

SYSDATETIMEOFFSET() as [sysdatetimeoffset]

我们尽量使用专门的学问的SQL,并不是TSQL,借使她们能代表成相仿的功用.举例获得当今天期时间时采取current_timestamp比GetDate()要好些.

cast是ANSI的标准SQL.

SELECT

SWITCHOFFSET(SYSDATETIMEOFFSET(),’+00:00′) AS
[UTCDATETIME],–能够把日子转成UTC的

CURRENT_TIMESTAMP AS [CURRENTDATE],

SYSUTCDATETIME() AS [SYSUTCDATETIME]

 

得到表的元数据音信:

exec sp_tables

EXEC sp_columns N’SCORE’,N’DBO’

EXEC sp_helpconstraint N’SCORE’

 

—-每种月最终一天生成的订单

–SELECT

–orderid,

–orderdate,

–custid,

–empid

–FROM

–SALES.ORDERS

–WHERE

–ORDERDATE = DATEADD(DD,-1,Convert(CHAR(7),DATEADD(mm,1,ORDERDATE),121)
+’-01′)

–ORDERDATE =
dateadd(month,datediff(month,’19991231′,orderdate),’19991231′)

 

–return lastname has a more than twice

–select

–empid,firstname,lastname

–from

–hr.employees

–where

–LEN(lastname) – len(replace(lastname,’a’,”))>1

–lastname like ‘%a%a%’

回来总价值超越10000的订单

–select

–orderid,

–SUM(qty*unitprice) as totalvalue

–from

–sales.orderdetails

–group by orderid

–having SUM(qty*unitprice) >10000

发表评论

电子邮件地址不会被公开。 必填项已用*标注