Getting the StartDate and EndDate from paramters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting the StartDate and EndDate from paramters

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-13 : 15:41:12

Hi,

I would liek to get the value for the Startdate in the format yyyymmdd based on the month and year passed.

example if month passed is 1 and year passed is 2005 then is there a way I can get the StartDate in the format yyyymmdd ?

Thanks

DECLARE
@StartMonth datetime,
@StartYear datetime,
@EndMonth datetime,
@EndYear datetime,
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'

SET @StartMonth = 1
SET @StartYear = '2005'
SET @EndMonth = 1
SET @EndYear = '2005'

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-13 : 16:47:10

I tried this but get ther foll error

DECLARE
@StartMonth varchar(12),
@StartYear varchar(12),
@EndMonth varchar(12),
@EndYear varchar(12),
@StartDate varchar(12), --= '20100101'
@EndDate varchar(12) --= '20100201'

SET @StartMonth = 1
SET @StartYear = '2005'
SET @EndMonth = 6
SET @EndYear = '2005'
SET @StartDate = @StartMonth + '/' + @StartYear
SET @EndDate = @EndMonth + '/' + @EndYear
/*1/2005 (StartMonth + StartYear)
1/2005 (EndMonth + EndYear)

where (mm,Inquirydate) + '/' + (yyyy,InquiryDate) BETWEEN concatenated dates*/
SELECT @StartMonth + '/' + @StartYear
SELECT @EndMonth + '/' + @EndYear

WHERE datepart(mm,'20050307') + '/' + datepart(yyyy,'20050307') BETWEEN @StartDate and @EndDate

(1 row(s) affected)
Msg 245, Level 16, State 1, Line 21
Conversion failed when converting the varchar value '/' to data type int.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-13 : 16:47:19

[code]
declare
@StartMonth int,
@StartYear int,
@EndMonth int,
@EndYear int

set @StartMonth = 1
set @StartYear = 2005
set @EndMonth = 11
set @EndYear = 2007

select StartDate = dateadd(month,(12*@StartYear)-22801+@StartMonth,0)

select EndDate = dateadd(month,(12*@EndYear)-22801+@EndMonth,0)[/code]
Results:
[code]StartDate
------------------------------------------------------
2005-01-01 00:00:00.000

(1 row(s) affected)

EndDate
------------------------------------------------------
2007-11-01 00:00:00.000

(1 row(s) affected)[/code]

Make Date function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

CODO ERGO SUM

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-13 : 17:08:00

Thanks.

What if i have the same month and year for Startdate and EndDate?

1/2005 and 1/2005? I should get

1/1/2005 and 1/31/2005 but I get 1/1/2005 for EndDate

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-13 : 17:45:53

If you have a date that is the first of the month you can add a month and then subtract one day:

DATEADD(DAY, -1, (DATEADD(MONTH, 1, @MyDate))
DATEADD(MONTH, 1, @MyDate) - 1

But, you can eliminate the DAY subtraction if you use just a LESS THAN (<) operator. That also saves you the issue of having to deal with the TIME portion of the datetime (if that is a factor).

CBrammer
Starting Member

7 Posts

Posted - 2012-06-21 : 16:04:50

I have a table that have startdate and enddate from a start and stop for a program.. I am trying to create a report that from a program as a parameter and a date parameter I can pull the records from the database based on the reports any ideas,

For example

Program ----- StartDate ----- BetweenDate ----- EndDate
CMP ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
CMP ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
CMP ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
CMP ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
LIPSC ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
LIPSC ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
LIPSC ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
LIPSC ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NHEMOG ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NHEMOG ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NHEMOG ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NHEMOG ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NINFO ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NINFO ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NINFO ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30
NINFO ----- 2009-07-01 ----- 2009-07-17 ------- 2009-09-30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources