Frequently used statements in SQL Server query dates, such as the first day of the week, the week of the year, useful

Source: Internet
Author: User
Tags sql server query

--本周第一天    SELECT DATEADD( Day ,1-(DATEPART(Weekday,getdate())[email protected]@DATEFIRST-1)%7,getdate())   --or    select dateadd(wk, datediff(wk,0,getdate()), 0)   --本周第一天    select dateadd(wk, datediff(wk,0,getdate()), 6)      --上月第一天    SELECT CONVERT ( CHAR (10),DATEADD( month ,-1,DATEADD(dd,- DAY (GETDATE())+1,GETDATE())),111)   --上月最后一天    SELECT CONVERT ( CHAR (10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+ ‘ 23:59:59‘ --本月第一天    select dateadd(dd,-datepart(dd,getdate())+1,getdate())   --本月最后一天    select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))   --本月天数    select datediff(dd,dateadd(dd,-datepart(dd,getdate())+1,getdate()), dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate())))   --or    select datepart(dd,dateadd(dd,-1,dateadd(mm,1, cast ( cast ( year (getdate())  as varchar )+ ‘-‘ + cast ( month (getdate())  as varchar )+ ‘-01‘ as datetime))))      --下月第一天    select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))   --下月最后一天    SELECT CONVERT ( CHAR (10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+ ‘ 23:59:59‘ --季度第一天    SELECT DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)    --季度最后一天(直接推算法)    SELECT DATEADD( Day ,-1, CONVERT ( char (8),DATEADD( Month ,1+DATEPART(Quarter,getdate())*3- Month (getdate()),getdate()),120)+ ‘1‘ )   --季度的最后一天(CASE判断法)    select DATEADD( Month ,DATEPART(Quarter,getdate())*3- Month (getdate()),getdate())      --本月第一个星期一    SELECT DATEADD(wk, DATEDIFF(wk,  ‘‘ , DATEADD(dd, 6 -  DAY (getdate()), getdate())),  ‘‘ )    --去年最后一天    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))    --今年第一天    SELECT DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)    --今年最后一天    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0))      --指定日期所在周的任意一天    SELECT DATEADD( Day ,@number-DATEPART(Weekday,@dt),@dt) --5.指定日期所在周的任意星期几    --A.  星期天做为一周的第1天    SELECT DATEADD( Day ,@number-(DATEPART(Weekday,@dt)[email protected]@DATEFIRST-1)%7,@dt)   --B.  星期一做为一周的第1天    SELECT DATEADD( Day ,@number-(DATEPART(Weekday,@dt)[email protected]@DATEFIRST-2)%7-1,@dt)   ---周内的第几日    select datepart(weekday,getdate())  as 周内的第几日   --年内的第几周    select datepart(week,getdate())  as 年内的第几周   --年内的第几季    select datepart(quarter,getdate())  as 年内的第几季         --判断某天是当月的第几周的sql函数     CREATE FUNCTION WeekOfMonth(@ day datetime)      RETURNS int AS begin    ----declare @day datetime    declare @num  int declare @Start datetime   declare @dd  int declare @dayofweek  char (8)   declare @dayofweek_num  char (8)   declare @startWeekDays  int ---set @day=‘2009-07-05‘    if datepart(dd,@ day )=1   return 1   else set @Start= ( SELECT DATEADD(mm,   DATEDIFF(mm,0,@ day ),   0))  --一个月第一天的    set @dayofweek= (datename(weekday,@Start))  ---得到本月第一天是周几    set @dayofweek_num=( select ( case @dayofweek  when ‘星期一‘ then 2   when ‘星期二‘ then 3   when ‘星期三‘ then 4   when ‘星期四‘ then 5   when ‘星期五‘ then 6   when ‘星期六‘ then 7   when ‘星期日‘ then 1   end ))   set @dayofweek_num= [email protected]_num+1  ---得到本月的第一周一共有几天    ---print @dayofweek_num        set @dd=datepart(dd,@ day ----得到今天是这个月的第几天    --print @dd    if @dd<[email protected]_num  --小于前一周的天数    return 1   else set @[email protected]@dayofweek_num   if @dd % 7=0         begin         set @[email protected] / 7            return @num+1                   end     else --if @dd % 7<>0                set @[email protected] / 7   set @[email protected]+1+1           return @num   end
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.