SQL 获取当前月的第一天最后一天获取当月最大最小的时间当月天数,当月第一天,当月最后一天

declare           

@firstday datetime,          

@lastday datetime          

set @firstday=convert(varchar(10),dateadd(day,-datepart(day,GETDATE())+1,GETDATE()),23)  +' 00:00:00'                                      

set  @lastday=convert(varchar(10),dateadd(day,-1,dateadd(month,1,GETDATE()-day(GETDATE())+1)),23)+' 23:59:59'   

select @firstday,@lastday  

  

declare   @a  datetime,--去年01-01 00:00:00  

@b datetime  --今年现在  

  set   @a =Convert(varchar,CONVERT(date,DATEADD(year,-1,dateadd(day,1-day(getdate()),dateadd(month,1-MONTH(getdate()),getdate())))))+' 00:00:00'  

  set   @b =DATEADD(yy,0,GETDATE())  

  select @a,@b  

 

SELECT     dbo.Departments.DepartmentName, dbo.WorkAreas.WorkAreaName, dbo.Medium.MediumName, dbo.Usage.Usage, dbo.Usage.UsageDate

FROM         dbo.Departments RIGHT OUTER JOIN

                      dbo.Usage ON dbo.Departments.DepartmentID = dbo.Usage.DepartmentID LEFT OUTER JOIN

                      dbo.Medium ON dbo.Usage.MediumID = dbo.Medium.MediumID LEFT OUTER JOIN

                      dbo.WorkAreas ON dbo.Departments.DepartmentID = dbo.WorkAreas.DepartmentID AND dbo.Usage.WorkAreaID = dbo.WorkAreas.WorkAreaID

WHERE     (dbo.Usage.SourceType = 2)

 

select DATEADD(DD,-DAY(getdate())+1,getdate())

 

select DATEADD(DD,-DAY(getdate()),DATEADD(m,1,getdate()))

 

获取当月最大和最小的时间

 

 

SELECT CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1')
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1')

 

SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1')+' 23:59:59.998'

 

源文档 <http://zhidao.123doing.com/67877.html>

 

 

  使用SQL语句取相关日期(当月天数,当月第一天,当月最后一天,本年最后一天,当月第一个星期) 收藏

--当月天数

select day(dateadd(ms,-3,DATEADD(m, DATEDIFF(m,0,getdate())+1,0))) 

---当月第一天

select   dateadd(d,-day(getdate())+1,getdate())  

---当月最后一天

select   dateadd(d,-day(getdate()),dateadd(m,1,getdate()))

--本年最后一天

select   dateadd(d,-day(getdate()),dateadd(m,12,getdate()))

--当月第一个星期一

SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')

 

源文档 <http://blog.youkuaiyun.com/songz210/archive/2009/05/19/4200960.aspx>

 

 

 

根据一个时间获取这个时间月份的第一天

 

select CONVERT(datetime,CONVERT(char(8),'2010-05-25 00:00:00.000',120)+'1')

 

项目中用到的，获取已知年份的第一天和最后一天，觉得网来的不错，留下做个念想，下次再用。

1.一个月第一天的

Java代码

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

 

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

2.本周的星期一

Java代码

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

 

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

3.一年的第一天

Java代码

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

 

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

4.季度的第一天

Java代码

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

 

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

5.当天的半夜

Java代码

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

 

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

6.上个月的最后一天

Java代码

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

 

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

7.去年的最后一天

Java代码

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

 

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

8.本月的最后一天

Java代码

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

 

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

9.本年的最后一天

Java代码

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

 

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

 

10.本月的第一个星期一

Java代码

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

 

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

 

 

 

 

        

C#获取当月的第一天、下月的第一天和最后一天

 

 

    DateTime Month = Convert.ToDateTime(UsageMonth);

 

            DateTime date0 = Month;

            //当月的第一天

            DateTime date1 = date0.Date.AddDays(1 - date0.Day);

            //下个月的第一天

            DateTime date2 = date0.Date.AddMonths(1);

            date2 = date2.AddDays(1 - date2.Day);

 

            DateTime t1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);

            //当月的最后一天

            DateTime t2 = t1.AddMonths(1).AddDays(-1);

            int days = t2.Day;

 

当前时间

select convert(datetime, convert(varchar(10),getdate(),120))

前一天时间（-1就是前一天）

select convert(datetime, convert(varchar(10),getdate()-1,120)) 

前两天时间

select convert(datetime, convert(varchar(10),getdate()-2,120)) 

 

 

 

 

随意一个时间的前一天

 

select dateadd(d,-1,getdate())

 

select dateadd(d,-1,'2010-10-16 11:00:00.000')

 

   

某天的某个时间点

 

select   distinct(convert(char(10),'2010-10-18 22:00:00.000',120))+ '   08:00:00 '

 

每天8天以前的最后一条记录，   

 

Create Table TEST(ID Int, TestTime DateTime)

Insert TEST Select 1, '2007-03-29 07:30:00'

Union All Select 2, '2007-03-29 08:00:00'

Union All Select 3, '2007-03-30 07:00:00'

Union All Select 4, '2007-03-31 07:00:00'

Union All Select 5, '2007-03-31 07:50:00'

GO

 

 

 

 

Select * From TEST A

Where Not Exists(Select 1 From TEST Where DateDiff(dd, TestTime, A.TestTime) = 0 And TestTime > A.TestTime And DatePart(Hour, TestTime) < 8)

And DatePart(Hour, TestTime) < 8

 

查询每天8点以前的最后一条记录，并且根据meterid分组

select * from Reading where Reading.SourceType = 1

 

Select * From Reading A

Where Not Exists(Select  1  From Reading Where DateDiff(dd, ReadTime, A.ReadTime) = 0

And ReadTime > A.ReadTime And DatePart(Hour, ReadTime) < 23 and Reading.SourceType = 1

and Reading.MeterID = A.MeterID)

and a.SourceType = 1 And DatePart(Hour, ReadTime) < 23