Regarding Excel Report in winform

Question

0.00/5 (No votes)

See more:

Hi guys any one can sugest me.

I'm using sql functions to fill date and day in the excel

In my user interface i have From date and To date.
1 April 2013--> From Date
30 April 2013-->To Date

Excel Report

Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday
..
30-03-13 Tuesday

Here day calculation i made it excel sheet formula itself.

=TEXT(A1, "dddd")

In My excel sheet i hard coded the formula for 31 days it is static

But i my user interface he may select less than 31 days

i.e 1-3-12 to 15-3-12

So i my excel sheet Day column after 15-03-13 all coming Saturday,Saturday only

How to solve this problem.

Bug Result:
Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday
Saturday
Saturday
Saturday
Expected Result
Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday

Posted 7-Apr-13 17:13pm

Salt Pepper

Add a Solution

Comments

Maciej Los 8-Apr-13 5:36am

SQL or Excel? I'm a bit confused...

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)

Kenneth Haugland · Answer 1 · 2013-04-07T18:52:00

Solution 1

Use daytime and the add function instead:
http://msdn.microsoft.com/en-us/library/system.datetime.add.aspx[^]

Posted 7-Apr-13 18:52pm

Kenneth Haugland

Maciej Los · Answer 2 · 2013-04-07T23:58:00

Excel (VBA):

VB

Sub EnumDates()

Dim curDate As Date, fromDate As Date, toDate As Date
Dim i As Integer

i = 1
fromDate = DateSerial(2013, 1, 1)
toDate = DateSerial(2013, 3, 31)
curDate = fromDate - 1
Do While curDate < toDate
    curDate = curDate + 1
    With ThisWorkbook.Worksheets(1).Range("A" & i)
        .NumberFormat = "yyyy-MM-dd"
        .Value = curDate
    End With
    With ThisWorkbook.Worksheets(1).Range("B" & i)
        .NumberFormat = "dddd"
        .Value = curDate
    End With
    i = i + 1
Loop

End Sub

SQL:

SQL

DECLARE @curDate DATETIME
DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME

SET @fromDate = '2013-01-01'
SET @toDate = '2013-03-31'
SET @curDate = @fromDate -1
;WITH dates AS
(
	SELECT @curDate +1 As MyDate, DATENAME(dw, @curDate +1) AS MyDayName
	UNION ALL
	SELECT MyDate +1, DATENAME(dw, MyDate +1) AS MyDayName
	FROM dates
	WHERE MyDate<@toDate	
)
SELECT *
FROM dates