How to create start date and end date with SQL?

Question

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

I want to get the data between 01/01/2012 and 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?

Bethsaida

What datatype is your column dates in your SQL Server table??? – Chyack

Also: when using strings for specifying dates, I would recommend to always use the ISO-8601 format : YYYYMMDD (or YYYY-MM-DDTHH:MM:SS with time) - this will work for any language/regional settings for your SQL Server - anything else is dependent on those settings and might break if those settings are wrong.... – Chyack

Answer 1

The following should give you the last day of the current month on 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))

Unthankful

Answer 2

This should do what you want. Not sure if it's the easiest way or not. @Parameter is your string that you're 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

Jactitation

It won't return rows where fldDate is e.g. 2012-01-31 08:23 for (st = 01/2012) - to be exact, it will not return rows for anything between 2012-01-31 00:00:00 (exclusive) and 2012-02-01 00:00:00 - records from last day of month. – Mocambique

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

Rowland

Answer 4

This works for me in 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.

Expiatory

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 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).

Lubin

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 around with the 101 which spesifies the date format. (Typically this is 101 or 103 which gives MM/dd/yyy or dd/MM/yyyy)

Rosary

Answer 7

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

Umbilical

Neither answers OP's question nor has any explanation at all! – Hyperventilation