how to get the first day and the last of previous

问题:

I am trying to get the first day of last month like 01/01/2013, also i want to get the last day of previous month like 01/31/2013. If we are in March then i want to do the same thing like 02/01/2013 and 02/28/2013 and so on.... thanks

回答1:

This should do it:

--First day of last month
SELECT DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))
--Last day of last month
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

回答2:

here is my solution

DECLARE @Today DATETIME
SELECT @Today = GETDATE()

  update  Test.dbo.DateTable
  set StartDate = (
SELECT convert (varchar, DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today)),101))

update Test.dbo.DateTable
set EndDate = (
SELECT convert(varchar, DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today)),101))

回答3:

First day of the last month

convert (date,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) 

Last Day of the previous month

convert (date,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))

回答4:

The following query works to find the last day of the prior month in my older MySQL:

SELECT DATE_SUB(CURDATE(),INTERVAL Extract(DAY from now()) DAY);