CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Gopal
Gopal

Reputation: 11982

How to create start date and end date with SQL?

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?

Upvotes: 5

Views: 65624

Answers (7)

Ilion
Ilion

Reputation: 6872

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

Upvotes: 3

Michał Powaga
Michał Powaga

Reputation: 23183

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

Upvotes: 1

James Hawks
James Hawks

Reputation: 19

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

Upvotes: 0

Charl
Charl

Reputation: 1002

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)

Upvotes: 1

Zo Has
Zo Has

Reputation: 13028

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.

Upvotes: 1

BenJP2k1
BenJP2k1

Reputation: 77

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

Upvotes: 2

rahularyansharma
rahularyansharma

Reputation: 10755

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

Upvotes: 1

Related Questions