如何使用 SQL 创建开始日期和结束日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8798723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create start date and end date with SQL?
提问by Gopal
string st = '01/2012' (MM/yyyy)
I want to get the data between 01/01/2012 and 31/01/2012
我想获取 01/01/2012 和 31/01/2012 之间的数据
How to create a start date and end date according to month and year format?
如何根据月份和年份格式创建开始日期和结束日期？
For Example
例如
st = 02/2012
Select * from table where dates between 01/02/2012 and 29/02/2012
How to make a query for adding start and end date of the month?
如何查询添加月份的开始和结束日期？
回答by Ilion
The following should give you the last day of the current month on sql-server-2000:
以下应该为您提供 sql-server-2000 上当月的最后一天：
SELECT DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0))
To find the last day of the month for a given month try:
要查找给定月份的最后一天，请尝试：
DECLARE @thisDate DATETIME
SET @thisDate = '06/27/2011'
SELECT DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@thisDate)+1,0))
回答by BenJP2k1
This should do what you want. Not sure if it's the easiest way or not. @Parameter is your string that you're passing.
这应该做你想做的。不确定这是否是最简单的方法。@Parameter 是您传递的字符串。
DECLARE @Parameter VARCHAR(7)
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SET @DateStart = CAST('01/' + @Parameter AS DATETIME)
SET @DateEnd = DATEADD(DD, -1, DATEADD(MM, 1, @DateStart))
SELECT * FROM tblTable WHERE fldDate BETWEEN @DateStart AND @DateEnd
回答by rahularyansharma
declare @m int
set @m=2
declare @y int
set @y=2012
declare @StartDate smalldatetime
declare @EndDate smalldatetime
set @StartDate=cast(@m as varchar(20))+'/'+'1/' +cast(@y as varchar(20))
print @StartDate
print datediff(day, @StartDate, dateadd(month, 1, @StartDate))
set @EndDate=cast(@m as varchar(20))+'/'+cast(datediff(day, @StartDate, dateadd(month, 1, @StartDate))as varchar(20))+'/' +cast(@y as varchar(20))
print @EndDate
回答by Zo Has
This works for me in DB2
这在 DB2 中对我有用
select (current date+1 month)-day(current date+1 month) days from sysibm.sysdummy1;
Edit: Current date function gives you date today, you can replace this with your input date.
编辑：当前日期功能为您提供今天的日期，您可以将其替换为您输入的日期。
回答by Micha? Powaga
Try:
尝试：
declare @st as varchar(10)
set @st = '01/2012'
select *
from table
where
dates >= convert(datetime, '01/' + @st, 103) and
dates < dateadd(mm, 1, convert(datetime, '01/' + @st, 103))
It returns all rows for specified month (inclusive whole last day of month till midnight). Mind >=(inclusive) and <(exclusive) signs. Quite important thing is that this is going to use index on column dates(if such is created).
它返回指定月份的所有行（包括一个月的最后一天到午夜）。心灵>=（包括）和<（排他）标志。非常重要的是，这将在列上使用索引dates（如果创建了索引）。
回答by Charl
This is for MS SQL:
这是针对 MS SQL：
DECLARE @datestring varchar(7) = '01/2012';
DECLARE @dateStart varchar(10) = CONVERT(varchar(10), '01/' + @datestring, 101);
DECLARE @dateEnd varchar(10) = CONVERT(varchar(10), DATEADD(DAY, -1, DATEADD(MONTH, 1, @dateStart)), 101);
SELECT
*
FROM
[StackTestDB].[dbo].[DateTable]
WHERE
[DateCol] >= @dateStart AND [DateCol] <= @dateEnd
Depending on the format of your dates, play around with the 101 which spesifies the date format. (Typically this is 101 or 103 which gives MM/dd/yyy or dd/MM/yyyy)
根据日期格式，使用指定日期格式的 101。（通常这是 101 或 103，它给出 MM/dd/yyy 或 dd/MM/yyyy）
回答by James Hawks
SELECT *
FROM table
WHERE MONTH(dates) = 2
AND YEAR(dates) = 2012
