基础练习,单表查询

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,’2000-10-15′)
,后天是周几=datename(weekday,’二〇〇二-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′,’2006-7-25 22:56:32′)重返值为 514592 秒
DateDiff(‘d’,’二零零七-07-20′,’2006-7-25 22:56:32′)再次来到值为 5 天

3.DatePart(‘w’,’2006-7-25 22:56:32′)再次回到值为 2
即星期四(周天为1,周末为7)
DatePart(‘d’,’二零零七-7-25 22:56:32′)重临值为 25即25号
DatePart(‘y’,’二〇〇五-7-25 22:56:32′)重返值为 206即这个时候中第206天
DatePart(‘yyyy’,’二〇〇七-7-25 22:56:32′)重临值为 二零零五即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

详尽的证实:

万般,你须要拿到超过天子和总括一些其余的日子,举个例子,你的前后相继或然需求推断一个月的第一天照旧最终一天。你们超过五成人差不离都了解怎样把日子举办分割(年、月、日等卡塔 尔(阿拉伯语:قطر‎,然后仅仅用分割出来的年、月、日等位居多少个函数中总计出自个儿所急需的日子!在此篇文章里,笔者将告诉你什么样行使DATEADD和
DATEDIFF函数来测算出在您的主次中大概你要用到的有的两样日期。
在行使本文中的例子以前,你必得注意以下的主题素材。大多数只怕不是持有例子在分裂的机械上进行的结果只怕不相仿,那全然由什么日期是一个礼拜的首后天那么些装置决定。第一天(DATEFI帕杰罗ST卡塔尔国设定调整了您的体系选用几时作为七日的首后天。全数以下的事例都是以周天看作二14日的率后天来树立,也便是第一天设置为7。要是你的第一天设置不均等,你可能供给调治这个事例,使它和分裂的率后天设置相符合。你能够经过@@DATEFIWranglerST函数来检查第一天设置。

为了了然那些事例,大家先复习一下
DATEDIFF和DATEADD函数。DATEDIFF函数总计七个日子之间的小时、天、周、月、年等日子间距总的数量。DATEADD函数总括二个日期通过给时间隔断加减来拿到叁个新的日子。要询问越来越多的DATEDIFF和DATEADD函数甚至时光间隔能够阅读微软联机帮忙。

使用
DATEDIFF和DATEADD函数来测算日期,和自然从此未来时此刻日子调换来你要求的日期的虚构方法有个别分裂。你必须要从岁月间隔这一个地点来虚构。举个例子,从领后天期到您要博取的日子之间有稍微时间间距,可能,从几天前到某一天(比方一九零三-1-1卡塔尔之间有多少日子间距,等等。精通什么重点于小运输间隔离有利于你轻轻便松的明白笔者的例外的日期总结例子。

二个月的首先天

先是个例证,笔者将告诉你怎样从最近不久期去当月的最后一天。请小心:那个事例以至那篇小说中的其余例子都将只行使DATEDIFF和DATEADD函数来总括我们想要的日子。每叁个例子都将通过总括但前的时间间距,然后开展加减来得到想要计算的日期。

那是简政放权一个月第一天的SQL 脚本:
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

大家把这么些讲话分开来探访它是怎样工作的。最中心的函数是getdate(),大多数人都理解这么些是回去当前的日子和岁月的函数。下多个进行的函数
DATEDIFF(mm,0,getdate())是计量当前几天子和“壹玖零贰-01-01
00:00:00.000”那个日期之间的月数。记住:时代和岁月变量和飞秒相近是从“1902-01-01
00:00:00.000”早先构思的。那即是怎么您能够在DATEDIFF函数中钦赐第多少个日子表明式为“0”。下多少个函数是DATEADD,扩大当今日子到“壹玖零壹-01-01”的月数。通过扩张预约义的日期“一九零一-01-01”和当下日子的月数,我们得以获取下一个月的率后天。其余,总结出来的日期的年月有些将会是“00:00:00.000”。

那一个总计的技能是先总括当前几天子到“一九〇二-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”之间的时光间距数量,然后把它加到“1905-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卡塔尔怎样获得有个别月的造化
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卡塔 尔(阿拉伯语:قطر‎决断是还是不是闰年:
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卡塔 尔(英语:State of Qatar)叁个季度多少天
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)  相差多少月 
从19981231到 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中华V子句提供窗口作为上下文,对窗口中的大器晚成组值进行操作,实际不是使用GROUP
BY 子句提供的上下文。那样能够同期再次来到根基行的列和聚合列。

ROW_NUMBEPRADO()
函数用于为查询的结果聚集的各行分配一个依次增加的连串号。其逻辑顺序通过OVE大切诺基子句中的O福睿斯DER
BY语句来进展点名。但固然OQashqaiDER
By的列不唯意气风发,则连串值也会追加,那样会造成再次来到的值不鲜明。

RANK 和 DENSE_RANK为有着相仿逻辑排序值的持有行生成相仿的排名。

RANK代表后面有多少行更低的排序值,而DENSE
RANK表示后面有稍许个不等的排序值。

 

能够感到在同意气风发SELECT子句中分歧不常间钦赐DISTINCT和ROW NUMBE奥迪Q7是一条最好履行,

因为DISTINCT在这里种情景下不起任何效果。

 

SELECT empid,firstname,lastname

FROM HR.Employees

WHERE lastname like N’D%’;

N表示National,用于表示字符串是Unicode数据类型(NCHASportage或NVARCHAEscort).

 

SQL中的三值逻辑,假若想回到region列不对等WA的享有行,如下:

SELECT custid,country,region,city

FROM Sales.Customers

WHERE region<>N’WA’

OR region IS NULL;

 

SQL帮衬三种字符数据类型:普通字符和Unicode字符。

平日字符使用一个字节来保存各类字符,所以节制那些列所帮忙的言语只可以是匈牙利(Hungary卡塔尔语。

 

使用定长的字符串时,SQLSEXC60VE中华V会预先分配相应的空间,使其不能扩展,它更适合以写入为主的系统,不过那连串型的仓库储存消耗不是最优的,读取的时候也许要付出越来越多的代价。

变长字符的囤积空间由实际数据量来调整,可是创新数据的时候可能会须要多少移动,超过当前页的限量,所以更新时的频率相对会低一些。

 

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

能够应用Replace来总计某字符在字符串中冒出的次数,方法是先把它替换到空,然后比对前后的差值再除以要协作的字符串的长短就可以了。

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

 

Date提姆eoffset 临时区部分能够拓宽甄别

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

发表评论

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