获取当前月份的日期范围？

发布于 2024-12-01 16:40:35 字数 332 浏览 1 评论 0原文

我想创建一个 SQL 语句，稍后在我的代码中使用，该语句获取当前月份的日期范围。

示例：这是八月，所以日期范围是，

StartDate = 08/01/11
EndDate = 08/31/11

但是，如果是二月，

StartDate = 02/01/11
EndDate = 02/28/11

Select * 
from mytable 
where (check_date >= StartDate) AND (check_date <= EndDate)

感谢您提供的任何帮助

原文

I would like to create a SQL statement to later be used in my code, that gets the date range for the current month.

Example: This is August, so the date range would be

StartDate = 08/01/11
EndDate = 08/31/11

however, if it was February

StartDate = 02/01/11
EndDate = 02/28/11

Select * 
from mytable 
where (check_date >= StartDate) AND (check_date <= EndDate)

thanks for any help you may be able to give

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

优雅的叶子 2024-12-08 16:40:35

您可以使用自零以来的月份技巧找到本月的开始。该月的最后一天是一个月后减去一天：

select  dateadd(month,datediff(month,0,getdate()),0)
,       dateadd(day,-1,dateadd(month,datediff(month,-1,getdate()),0))

这将打印：

1-aug-2011    31-aug-2011

The you can find the start of this month with the months-since-zero trick. The last day of the month is one month later, minus one day:

select  dateadd(month,datediff(month,0,getdate()),0)
,       dateadd(day,-1,dateadd(month,datediff(month,-1,getdate()),0))

This prints:

1-aug-2011    31-aug-2011

回复收藏 0 原文

王权女流氓 2024-12-08 16:40:35

我需要非常类似的东西，从特定日期获取月份的日期范围，这可以在 SQL 搜索中使用 - 这意味着不仅日期必须正确，而且时间必须从 00:00:00 到 23:59:59 如果在 24 小时制中，但只要显示 DATETIME 格式即可，它也适用于 12 小时制。

也许这对某人有用。该解决方案基于 Andomar 的回答：

-- Parameter date, which must be given to this code
DECLARE @date DATETIME
SET @date = GETDATE() -- for testing purposes initializing some date

-- Declare @from and to date range variables
DECLARE @from DATETIME
DECLARE @to DATETIME

-- This code line is based on Andomar's answer
SET @from = DATEADD(month,DATEDIFF(month, 0, @date),0) 

-- Just simply to variable @from adds 1 month, minus 1 second
SET @to = DATEADD(second, -1, DATEADD(month, 1, @date))

-- Result
SELECT @from, @to

您将得到类似 2012.01.01 00:00:00 - 2012.01.31 23:59:59 的结果。

I needed very similar thing, to get month's date range from specific date, which was possible to use in SQL search - that means not only dates must be correct, but time must be from 00:00:00 to 23:59:59 if in 24 hour system, but it just matter of displaying DATETIME format, it will work with 12 hours system too.

Maybe it will be useful for someone. This solution is based on Andomar's answer:

-- Parameter date, which must be given to this code
DECLARE @date DATETIME
SET @date = GETDATE() -- for testing purposes initializing some date

-- Declare @from and to date range variables
DECLARE @from DATETIME
DECLARE @to DATETIME

-- This code line is based on Andomar's answer
SET @from = DATEADD(month,DATEDIFF(month, 0, @date),0) 

-- Just simply to variable @from adds 1 month, minus 1 second
SET @to = DATEADD(second, -1, DATEADD(month, 1, @date))

-- Result
SELECT @from, @to

You will get result like 2012.01.01 00:00:00 - 2012.01.31 23:59:59.

回复收藏 0 原文

国产ˉ祖宗 2024-12-08 16:40:35

如果您需要月份范围只是为了检查“check_date”是否属于特定月份，您也许可以使用类似的条件

month(Check_date) = @Month and year(Check_date) = @Year

If you need the month range just for checking if "check_date" belongs to a specific month, you maybe can use a condition like

month(Check_date) = @Month and year(Check_date) = @Year

回复收藏 0 原文

南街女流氓 2024-12-08 16:40:35

您可以创建一个返回给定日期的结束日期的函数。

所以你可以传递开始日期的函数，它会像这样

WHEN(MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12)) THEN 31
WHEN(MONTH(@date) IN (4, 6, 9, 11)) THEN 30
ELSE 
    CASE 
       WHEN (YEAR(@date) % 4 = 0
       AND YEAR(@date) % 100 != 0)
       OR (YEAR(@date) % 400  = 0)
       THEN 29
        ELSE 28
END

You could create a function that returns the end date for the given date.

So the function you can pass the start date and it will be like this

WHEN(MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12)) THEN 31
WHEN(MONTH(@date) IN (4, 6, 9, 11)) THEN 30
ELSE 
    CASE 
       WHEN (YEAR(@date) % 4 = 0
       AND YEAR(@date) % 100 != 0)
       OR (YEAR(@date) % 400  = 0)
       THEN 29
        ELSE 28
END

回复收藏 0 原文

~没有更多了~