Click here to Skip to main content
15,902,876 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

My TRANSACT SQL QUERY accepts @Start_Date='2016-06-3 13:46:25.370' as a parameter and returns the results:

ColA	ColB	ColC
'2016-06-3 13:46:25.370'	B	C


I want to pass another parameter @End_Date='2016-06-5 13:46:25.370' and get the result:

ColA	ColB	ColC
'2016-06-3 13:46:25.370'	B	C
'2016-06-4 13:46:25.370'	B	C
'2016-06-5 13:46:25.370'	B	C

Basically, I am trying to loop through the dates as parameter.

My SQL Query is:

SQL
DECLARE @Start_Date as Datetime='2016-06-3 13:46:25.370', @End_Date as datetime='2016-06-3 13:46:25.370'
SELECT @Start_Date [ColA], 'B' [ColB], 'C' [ColC] 

Any help will be highly appreciated.
Posted
Updated 27-Jun-16 0:45am
v2

Try:
C#
SELECT * FROM MYTable WHERE MyDateColumn BETWEEN @Start_Date AND @End_Date

But... If those are your column values and you are storing dates in VARCHAR or NVARCHAR columns, then you need to fix that first. Never store dates (or anything else other than genuine string values like names and addresses) in string columns - it gives huge problems later when you try to use them, because string ordering is based on the first different character in two strings.
 
Share this answer
 
Comments
ZohaibRazaTheDProgrammer 27-Jun-16 6:36am    
@OriginalGriff if you can please read my query, I am NOT running the query on any table and there is no column containing dates. - Regards
If you're looking for solution to "produce" some portion of data having two dates as input parameters, you may be interested in CTE[^].
SQL
DECLARE @Start_Date DATETIME ='2016-06-03 13:46:25.370'
DECLARE @End_Date DATETIME ='2016-06-05 13:46:25.370'

;WITH MyDates AS
(
	SELECT @Start_Date AS MyDate
	UNION ALL
	SELECT DATEADD(DD,1, MyDate) AS MyDate
	FROM MyDates
	WHERE DATEADD(DD,1, MyDate)<=@End_Date
)
SELECT *
FROM MyDates
--OPTION(MAXRECURSION 0) --uncomment this lie, if the count of dates exceedes 100


Result:
MyDate
2016-06-03 13:46:25.370
2016-06-04 13:46:25.370
2016-06-05 13:46:25.370
 
Share this answer
 
Comments
ZohaibRazaTheDProgrammer 27-Jun-16 10:15am    
@Maciej Los you query works awesome but it shows me a Syntax Error when I try it this way:
(
SELECT @Start_Date AS MyDate, ColA From A_Table
UNION ALL
SELECT DATEADD(DD,1, MyDate) AS MyDate, ColB FROM B_Table
FROM MyDates
WHERE DATEADD(DD,1, MyDate)<=@End_Date
)
Can you please help me modify this query to my best use. - Regards
Maciej Los 27-Jun-16 12:19pm    
Well... You're doing it wrong way. You have to join ColA and ColB at the end of statement, just after second ")":
SELECT md.MyDate, a.ColA, b.ColB
FROM MyDate AS md
INNER JOIN TableA AS a ON md.MyDate = a.OtherDateField
INNER JOIN TableB AS b ON md.MyDate = b.AnotherDateField

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900