How to loop through the date as parameter of SQL query?

Question

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 27-Jun-16 0:15am

ZohaibRazaTheDProgrammer

Updated 27-Jun-16 0:45am

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

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.

Posted 27-Jun-16 0:32am

OriginalGriff

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2016-06-27T00:45:00

Solution 2

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

Posted 27-Jun-16 0:45am

Maciej Los

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