How to create SQL start and end date?

Question

string st = '01/2012' (MM/yyyy) 

I want to get data between 01/01/2012 and 01/31/2012

How to create a start date and end date in accordance with the format of the month and year?

Example

 st = 02/2012 Select * from table where dates between 01/02/2012 and 29/02/2012 

How to make a request to add a start and end date for a month?

Answer 1

The following should provide you with the last day of the current month in sql-server-2000:

 SELECT DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0)) 

To find the last day of a 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)) 

Answer 2

This should do what you want. Not sure if this is the easiest way or not. @Parameter is your string you are passing.

 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 

Answer 3

 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 

Answer 4

This works for me in DB2

 select (current date+1 month)-day(current date+1 month) days from sysibm.sysdummy1; 

Edit: The current date function gives you the date today, you can replace it with your input date.

Answer 5

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 the specified month (including the entire last day of the month until midnight). Reason >= (inclusive) and < (exclusive) signs. It is very important that this will use the index in the dates column (if one is created).

Answer 6

This is for 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 with 101, which simplifies the date format. (Usually it is 101 or 103, which gives MM / dd / yyy or dd / MM / yyyy)

Answer 7

 SELECT * FROM table WHERE MONTH(dates) = 2 AND YEAR(dates) = 2012