关于日期操作

1：获得指定日期指定月份的第一天

formular form:dateadd(month,datediff(month,'19000101',orderdate),'19000101')

select distinct dateadd(month,datediff(month,'19000101',orderdate),'19000101')
from sales.ordervalues

 

 

2：获得指定日期指定月份的最后一天

formular form: dateadd(month,datediff(month,'19000131',orderdate),'19000131')

select distinct dateadd(month,datediff(month,'19000131',orderdate),'19000131')
from sales.ordervalues

 

 

3:获得指定日期指定月份的第一天

select dateadd(month,datediff(month,0,o.orderdate),0)
as ordmonth
from
TSQLFundamentals2008.sales.orders as o

 

3 获得指定日期的年月这两个部分

select convert(nvarchar(6),orderdate,112)
from sales.ordervalues

 

获得员工的生日(nearest birthday)

WITH Args1 AS
(
  SELECT LastName, FirstName, BirthDate,
    DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
    CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
  FROM dbo.Employees
),

Args2 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    DATEADD(year, Diff, BirthDate) AS BDCur,
    DATEADD(year, Diff + 1, BirthDate) AS BDNxt
  FROM Args1
),
Args3 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
      THEN 1 ELSE 0 END AS BDCur,
    BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
      THEN 1 ELSE 0 END AS BDNxt
  FROM Args2
)
SELECT LastName, FirstName, BirthDate,
  CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
FROM Args3;