| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> mssql >> chu xue zhe bi du : jing jiang SQL zhong de shi jian ji suan yu ju
 

chu xue zhe bi du : jing jiang SQL zhong de shi jian ji suan yu ju

2008-08-15 06:29:25 编辑來源:互联网 评论
 
本文为【初学者必读:精讲SQL中的时间计算语句】的拼音翻译版
  wen : qing wen , ru he ji suan yi ge biao zhong de zhou qi shi he jie zhi ri qi bing xie dao biao zi duan zhong ? wo yao cong yi ge biao xiang ling yi ge biao dao ru shu ju , bing jin xing zhuan huan , yong de shi VB 。
  wo xian zai you you yi ge biao zhu yao zi duan you
  time_id int
  time_date datetime
  year int
  week_of_year int
  day nvarhar
  xiang yao zhuan huan cheng ling wai yi zhang biao
  time_id int
  time_date datetime
  year int
  week_of_year nvarchar
  yuan lai de biao nei rong shi
  --------------------
  1 2003-07-09 2003 20 xing qi ri
  1 2003-07-10 2003 20 xing qi yi
  1 2003-07-11 2003 20 xing qi er
  xiang yao bian cheng
  --------------------
  1 07/09/2003 2003 di 20 zhou (7/9-7/17)
  1 07/10/2003 2003 di 20 zhou (7/9-7/17)
  1 07/11/2003 2003 di 20 zhou (7/9-7/17)
  qing wen : zhe ge yu ju ying gai zen me qu xie ?
  da : if object_id('tablename') is not null drop table tablename
  select 1 as time_id, '2003-07-09' as time_date, 2003 as [year], 20 as week_of_year, ' xing qi ri ' as [day]
  into tablename
  union select 1, '2003-07-10', 2003, 20, ' xing qi yi '
  union select 1, '2003-07-11', 2003, 20, ' xing qi er '
  ------------------------------------------------
  select time_id, time_date, [year], ' di ' + cast(week_of_year as varchar(2)) + ' zhou ('
  + cast(month(week_begin) as varchar(2)) + '/' + cast(day(week_begin) as varchar(2)) + '-'
  + cast(month(week_end) as varchar(2)) + '/' + cast(day(week_end) as varchar(2)) as week_of_year
  from (select *, dateadd(day, 1 - datepart(weekday, time_date), time_date) as week_begin,
  dateadd(day, 7 - datepart(weekday, time_date), time_date) as week_end from tablename) a
  /*
  time_id time_date year week_of_year
  1 2003-07-09 2003 di 20 zhou (7/6-7/12)
  1 2003-07-10 2003 di 20 zhou (7/6-7/12)
  1 2003-07-11 2003 di 20 zhou (7/6-7/12)
  */
  ------------------------------------------------
  drop table tablename
  wen ti sui ran jie jue le , dan zhe ge li zi bing bu ju bei tong yong xing , huan shi ge an , suo yi wo men fen xi le ni de dai ma , fa xian yi ge wen ti : ri qi fan wei shi ru he que ding de ? suo yi , wo men ba ta yan shen fa san dao : neng fou zi zhu she ding ri qi de fan wei ne ? bi ru she ding dao xing qi yi huo xing qi tian kai shi :
  si lu :
  SET DATEFIRST
  jiang yi zhou de di yi tian she zhi wei cong 1 dao 7 zhi jian de yi ge shu zi 。
  yu fa
  SET DATEFIRST { number | @number_var }
  shen shu
  number | @number_var
  shi yi ge zheng shu , biao shi yi zhou de di yi tian , ke yi shi xia lie zhi zhong de yi ge 。
  zhi yi zhou de di yi tian shi
  1 xing qi yi
  2 xing qi er
  3 xing qi san
  4 xing qi si
  5 xing qi wu
  6 xing qi liu
  7( mo ren zhi , mei guo ying yu ) xing qi ri
  zhu shi
  shi yong @@DATEFIRST han shu jian cha SET DATEFIRST de dang qian she zhi 。
  SET DATEFIRST de she zhi shi zai zhi xing huo yun xing shi she zhi , er bu shi zai fen xi shi she zhi 。
  fang fa yi :
  xian chuang jian yi ge yong hu han shu , gen ju ri qi de dao zhou kai shi ri qi he jie shu ri qi
  create function getwstend(@resultdate datetime)
  returns varchar(20)
  As
  begin
  declare @weekstart datetime,@weekend datetime,@dayweek int,@weeknum int
  set @weeknum = datepart(wk,@resultdate)
  set @dayweek = datepart(dw,@resultdate)
  begin
  set @weekstart = dateadd(dd,1-@dayweek,@resultdate)
  set @weekend = DateAdd(dd,7-@dayweek,@resultdate)
  end
  return ' di '+Convert(varchar,@weeknum)+' zhou '+'('+right(convert(varchar(10),@weekstart,111),5)+'--'+right(Convert(varchar(10),@weekend,111),5)+')'
  end
  Go
  li ru :
  select test.getwstend('2006-4-17')
  fan hui jie guo shi : di 16 zhou (04/16--04/22)
  li yong zhe ge han shu xiang xin jiu ke yi shi xian ni de yao qiu le , ba suo you biao 1 zhong de shu ju zhuan dao biao 2
  fang fa er :
  wei le bi mian shu ju de rong yu , jian yi bu yao ba shu ju cong di yi ge biao zhuan dao di er ge biao 。 yin wei zhe yang hui zao cheng shu ju rong yu 。 ni ke yi zai cha xun shi hou shi yong han shu chan sheng week_of_year nvarchar zhe ge lan wei 。
  fang fa san :
  chuang jian yi ge biao , lan wei wei :
  create table tw
  (
  time_date datetime,
  week_of_year nvarchar
  )
  ran hou 1910.1.1---2050.12.31 zhi jian de suo you ri qi dou chan sheng yi ge dui zhao de week_of_year nvarchar, fang fa ye shi shi yong shang mian chuang jian de han shu 。 ran hou cha xun de shi hou 。 zhi yao liang ge biao zuo guan lian jiu ke yi le
  ru guo yuan shi biao zhong de ji lu hen duo 。 er qie ri qi you hen duo chong fu , wo men geng tui jian shi yong fang fa san 。【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
wen : qing wen , ru he ji suan yi ge biao zhong de zhou qi shi he jie zhi ri qi bing xie dao biao zi duan zhong ? wo yao cong yi ge biao xiang ling yi ge biao dao ru shu ju , bing jin xing zhuan huan , yong de shi VB 。 wo xian zai you you yi ge biao zhu yao zi duan you time_id int time_date datetime year int week_of_year int day nvarhar xiang yao zhuan huan cheng ling wai yi zhang biao time_id int time_date datetime year int week_of_year nvarchar yuan lai de biao nei rong shi -------------------- 1 2003-07-09 2003 20 xing qi ri 1 2003-07-10 2003 20 xing qi yi 1 2003-07-11 2003 20 xing qi er xiang yao bian cheng -------------------- 1 07/09/2003 2003 di 20 zhou (7/9-7/17) 1 07/10/2003 2003 di 20 zhou (7/9-7/17) 1 07/11/2003 2003 di 20 zhou (7/9-7/17) qing wen : zhe ge yu ju ying gai zen me qu xie ? da : if object_id('tablename') is not null drop table tablename select 1 as time_id, '2003-07-09' as time_date, 2003 as [year], 20 as week_of_year, ' xing qi ri ' as [day] into tablename union select 1, '2003-07-10', 2003, 20, ' xing qi yi ' union select 1, '2003-07-11', 2003, 20, ' xing qi er ' ------------------------------------------------ select time_id, time_date, [year], ' di ' + cast(week_of_year as varchar(2)) + ' zhou (' + cast(month(week_begin) as varchar(2)) + '/' + cast(day(week_begin) as varchar(2)) + '-' + cast(month(week_end) as varchar(2)) + '/' + cast(day(week_end) as varchar(2)) as week_of_year from (select *, dateadd(day, 1 - datepart(weekday, time_date), time_date) as week_begin, dateadd(day, 7 - datepart(weekday, time_date), time_date) as week_end from tablename) a /* time_id time_date year week_of_year 1 2003-07-09 2003 di 20 zhou (7/6-7/12) 1 2003-07-10 2003 di 20 zhou (7/6-7/12) 1 2003-07-11 2003 di 20 zhou (7/6-7/12) */ ------------------------------------------------ drop table tablename wen ti sui ran jie jue le , dan zhe ge li zi bing bu ju bei tong yong xing , huan shi ge an , suo yi wo men fen xi le ni de dai ma , fa xian yi ge wen ti : ri qi fan wei shi ru he que ding de ? suo yi , wo men ba ta yan shen fa san dao : neng fou zi zhu she ding ri qi de fan wei ne ? bi ru she ding dao xing qi yi huo xing qi tian kai shi : si lu : SET DATEFIRST jiang yi zhou de di yi tian she zhi wei cong 1 dao 7 zhi jian de yi ge shu zi 。 yu fa SET DATEFIRST { number | @number_var } shen shu number | @number_var shi yi ge zheng shu , biao shi yi zhou de di yi tian , ke yi shi xia lie zhi zhong de yi ge 。 zhi yi zhou de di yi tian shi 1 xing qi yi 2 xing qi er 3 xing qi san 4 xing qi si 5 xing qi wu 6 xing qi liu 7( mo ren zhi , mei guo ying yu ) xing qi ri zhu shi shi yong @@DATEFIRST han shu jian cha SET DATEFIRST de dang qian she zhi 。 SET DATEFIRST de she zhi shi zai zhi xing huo yun xing shi she zhi , er bu shi zai fen xi shi she zhi 。 fang fa yi : xian chuang jian yi ge yong hu han shu , gen ju ri qi de dao zhou kai shi ri qi he jie shu ri qi create function getwstend(@resultdate datetime) returns varchar(20) As begin declare @weekstart datetime,@weekend datetime,@dayweek int,@weeknum int set @weeknum = datepart(wk,@resultdate) set @dayweek = datepart(dw,@resultdate) begin set @weekstart = dateadd(dd,1-@dayweek,@resultdate) set @weekend = DateAdd(dd,7-@dayweek,@resultdate) end return ' di '+Convert(varchar,@weeknum)+' zhou '+'('+right(convert(varchar(10),@weekstart,111),5)+'--'+right(Convert(varchar(10),@weekend,111),5)+')' end Go li ru : select test.getwstend('2006-4-17') fan hui jie guo shi : di 16 zhou (04/16--04/22) li yong zhe ge han shu xiang xin jiu ke yi shi xian ni de yao qiu le , ba suo you biao 1 zhong de shu ju zhuan dao biao 2 fang fa er : wei le bi mian shu ju de rong yu , jian yi bu yao ba shu ju cong di yi ge biao zhuan dao di er ge biao 。 yin wei zhe yang hui zao cheng shu ju rong yu 。 ni ke yi zai cha xun shi hou shi yong han shu chan sheng week_of_year nvarchar zhe ge lan wei 。 fang fa san : chuang jian yi ge biao , lan wei wei : create table tw ( time_date datetime, week_of_year nvarchar ) ran hou 1910.1.1---2050.12.31 zhi jian de suo you ri qi dou chan sheng yi ge dui zhao de week_of_year nvarchar, fang fa ye shi shi yong shang mian chuang jian de han shu 。 ran hou cha xun de shi hou 。 zhi yao liang ge biao zuo guan lian jiu ke yi le ru guo yuan shi biao zhong de ji lu hen duo 。 er qie ri qi you hen duo chong fu , wo men geng tui jian shi yong fang fa san 。
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
时尚性感的燕子(3)
时尚性感的燕子(2)
纯美的女生生活写真(14)
纯美的女生生活写真(13)
来自束河[]
恰巧-巧家~~
高尔夫
行摄匆匆天子山
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有