How do I get the number of records from StartDate to EndDate?

Question

0.00/5 (No votes)

See more:

Hi All,

I have two date time variables @StartDate='01 Jan 2013' and @EndDate='10 Jan 2013'
I need to insert data in a table without using any loop as below

EmpCode Date
1       1-Jan-14
1       2-Jan-14
1       3-Jan-14
1       4-Jan-14
1       5-Jan-14
1       6-Jan-14
1       7-Jan-14
1       8-Jan-14
1       9-Jan-14
1       10-Jan-14

Is there any way or join so that we get the data like above.

Thanks in advance...

Posted 24-Jan-14 19:54pm

rakesh@bbspl.com

Updated 25-Jan-14 11:51am

André Kraak

v3

Add a Solution

Comments

Peter Leow 25-Jan-14 3:40am

Not clear, what have @StartDate and @EndDate got to do with the rest of data like '1-Jan 14' and so on. Please elaborate using 'Improved question' button.

rakesh@bbspl.com 25-Jan-14 7:16am

Hello Peter,

I just need to write a query which can result 10 rows as mention above.
and i have three variables @EmpCode=1, @Startdate='01 Jan 2014' and @Enddate='10 Jan 2014'

Thanks in advance...

Peter Leow 25-Jan-14 7:49am

In your question, they were stated as @StartDate='01 Jan 2013' and @EndDate='10 Jan 2013' not 2014. That is the missing link.
And do you want to 'insert' these data into or 'retrieve' them from the database?

2 solutions

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)

Abhinav S · Answer 1 · 2014-01-24T20:29:00

Solution 1

You could use something like

SQL

Insert into table 1 values 
(select * from table2 where table2.date1 > startdate and table2.date2 < enddate)>

In addition, try http://www.techonthenet.com/sql/between.php[^].

Posted 24-Jan-14 20:29pm

Abhinav S

v2

Comments

rakesh@bbspl.com 25-Jan-14 3:18am

I don't have table2, only need to insert the data inot table1

Jörgen Andersson · Answer 2 · 2014-01-25T11:40:00

Normally I would have told you to use a recursion

SQL

WITH Dates AS (
        SELECT  [Date] = @startdate
        UNION ALL
        SELECT  [Date] = DATEADD(DAY, 1, [Date])
        FROM    Dates
        WHERE   Date < @enddate)
SELECT  [Date]
FROM    Dates

But as you have this strange demand that you don't want to use any loops, I searched and found this oddity on Stackoverflow.

SQL

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24'

It's actually quite performant.
If you need more than a thousand days you need to add more source tables to the cross join.