关于日期操作

最新推荐文章于 2024-07-29 22:15:27 发布

pmandy

最新推荐文章于 2024-07-29 22:15:27 发布

阅读量105

点赞数

分类专栏： sql

sql 专栏收录该内容

21 篇文章

订阅专栏

本文介绍如何使用SQL语句获取指定日期的指定月份的第一天、最后一天、年份与月份，以及计算员工的下一个生日日期。涵盖了基本的SQL日期函数应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;