| Author |
 Topic  |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
 Posted - 04/13/2010 : 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 - 04/13/2010 : 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)
USA
7007 Posts |
Posted - 04/13/2010 : 16:47:19
|
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)
Results:
StartDate
------------------------------------------------------
2005-01-01 00:00:00.000
(1 row(s) affected)
EndDate
------------------------------------------------------
2007-11-01 00:00:00.000
(1 row(s) affected)
Make Date function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 04/13/2010 16:48:24 |
|
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 04/13/2010 : 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
Flowing Fount of Yak Knowledge
3857 Posts |
Posted - 04/13/2010 : 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). |
Edited by - Lamprey on 04/13/2010 17:46:31 |
|
|
|
CBrammer
Starting Member
USA
7 Posts |
Posted - 06/21/2012 : 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 |
Edited by - CBrammer on 06/21/2012 16:18:49 |
|
|
| |
Topic |
|
Getting the StartDate and EndDate from paramters
